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)
Month: April 2009
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
</code>
I confirmed this had worked with the ts-free script (attached) …
<code>column owner format a10
column “%free” format 999
select a.tsname, a.total “Tot/Kb”, a.total – b.free “Used/Kb”,
b.free “Free/K”, (b.free * 100) / a.total “%free”
from
(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” %*
exit
… 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
<?php
@mysql_connect(“localhost”,”root”,”[password]”);
$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’.
Database Consultant 