Oracle Financials ‘Active Users’ report.

To get a report of Active Users for a customer I
– logged in as ‘sysadmin’.
– Started Systems Administrator
– Chose Requests / Run / Single Request
– Typed ‘active’ in the Name field, and clicked Submit.
– Chose Active Users, ok and ok again.
– Clicked Refresh Data until I found the ‘Active Users’ job complete …
– Chose View Output, then Tools / Copy File, File / Save as
– Chose the location as h-drive (a shared drive) / Sequent / ANN (in this case) and clicked Save.
– I exited Oracle Financials
– Typed ‘net use’ at a command prompt to find the path to the h-drive
– And emailed the file’s name and location to the customer (eg: \NW-GIR-TSTSSHARESequentANN)

Tablespace has only 1% free

My daily checks reveiled that tablespace D_LARGE3 had only 1% free (NB: these are manual extend).
I listed the current datafile’s to see the path and name, and to see how large the current ones were …

<code>col tablespace_name for a20

col file_name for a40

select tablespace_name, file_name, bytes/1024/1024

from dba_data_files

where tablespace_name = ‘D_LARGE3’

order by file_name;

TABLESPACE FILE_NAME                                BYTES/1024/1024———- —————————————- —————D_LARGE3   /db010/oradata/CIX/d_large3_01.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_02.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_03.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_04.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_05.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_06.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_07.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_08.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_09.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_10.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_11.dbf                  1985

TABLESPACE FILE_NAME                                BYTES/1024/1024———- —————————————- —————D_LARGE3   /db010/oradata/CIX/d_large3_12.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_13.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_14.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_15.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_16.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_17.dbf                  1985D_LARGE3   /db010/oradata/CIX/d_large3_18.dbf                  1985D_LARGE3   /db013/oradata/CIX/d_large3_19.dbf                  1000</code>

… and noticed the last one was only 1GB in size. Therefore I didn’t need to add a new datafile, just expand it …

<code>alter database datafile ‘/db013/oradata/CIX/d_large3_19.dbf’ resize 2000m


I confirmed this had worked with the ts-free script (attached) …

<code>column owner format a10

column “%free” format 999

select a.tsname, “Tot/Kb”, – “Used/Kb”, “Free/K”, ( * 100) / “%free”


(select tablespace_name tsname, sum(bytes)/1024 total

from dba_data_files

group by tablespace_name ) a,   (select tablespace_name tsname, sum(bytes)/1024 free     from dba_free_space     group by tablespace_name ) b   where a.tsname = b.tsname(+)/
TSNAME                             Tot/Kb    Used/Kb     Free/K %free—————————— ———- ———- ———- —–D_LARGE1                         58312704   56477696    1835008     3D_LARGE3                         56929280   56142848     786432     2</code>
job done :-))
btw <a href=”/node/179″>here’s</a> a similar incident were I had to add a datafile

How big is the current Oracle instance?

Here’s some code to report the size or the current Oracle instance …

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p

Useful Windows commands

This will be my repository for tried-and-tested useful Windows commands …

1) This is my perpetual alarm-lock whilst working-at-home during the week. Stops me clock-watching, and lets me know when I’m doing overtime. (Don’t forget to check the messenger-service is started).

at 17:00 /every:M,T,W,Th,F net send “richard” Its Five O’Clock lol

2) In the path (EG: C:Windows) I create a file ‘remindme.cmd’ containing the code …

at %1 net send “richard” %*

… then at some time in my busy future I can set a simple, robust, reminder (robust as I don’t need anything running – like an email client). I just open a command prompt and type something like ‘remindme 13:58 call joe for lunch’.
(Oh, and don’t forget to check the messenger-service is working).

MySQL monitoring tools

“AjaxMyTop” is a really useful MySQL monitoring tool.

If you’re on a web-server, and therefore have apache and mysql readily available you can easily implement a live web-page version of the Unix ‘top’ command.
– Download and unpack the ‘ajaxMyTop’ folder into htdocs.
– Configure the config.php file with user (root?), and password
– Then browse to http://localhost/ajaxMyTop/ and wait.

After a few seconds you should at-the-least see a line for ‘show processlist’ generated by the tool.

Testing Apache, PHP, and MySQL.

Here’s the simplest test-page I can think of that will test …
– the supplied credentials
– PHP can connect to MySQL
– Apache can display the results

$result = mysql_query(“show databases”);
echo “<h1>Databases this user can connect to …</h1>”;
while ($row=mysql_fetch_array($result)) echo($row[“Database”].”<br />”);

Paste it into a file called ‘test.php’ (with the correct password) in Apache’s ‘htdocs’ folder, then browse ‘http://localhost/test.php&#8217;.