Use
rm -ir (dir name)
to remove an empty or non-empty UNIX directory. The ‘i’ option is a sensible precaution to help avoid mistakes. It brings up the name of the file / directory being deleted with a ‘delete Yes/No?’ option.
To locate tnsnames … echo $TNS_ADMIN.
MSDE is the SQL2000 version of SQL Express
To interact with the server you should type osql -E at the command-prompt. This connects to the default MSDE local instance.
Having completed building a new application a customer wanted to upgrade there back-end database from SQL Express to full SQL Server 2005 for licencing issues.
Here’s my walk through …
Having already installed IIS and navigated within a command-prompt to the media (cd “D:AppsSQL 2005Disk 1”). I ran “setup.exe SKUUPGRADE=1”.
I accepted the licencing conditions, and continued installing components
1) SQL Server Database Services
2) Integration services
(resisting the temptation to re-assign the datafiles from the small C-Drive to the larger D-Drive), until Getting to the ‘Instance Name’ screen where I clicked the ‘Installed Instances’ button.
The next screen confirms the SQL Express instance name, I clicked ‘OK’, then with the instance-name autocompleted clicked Next.
In the ‘existing components’ screen I ticked the one box to request an upgrade and clicked ‘next’.
–NOTES
The resulting upgrade is not obvious, however it can be confirmed my right-clicking on the server in enterprize manager and examining the version.
Following on from this SP3 should be applied.
To action the request to create a full daytime export on the GEMS database on LSC3. I first deleted the previous export from ‘/tmp/exports’ to create the needed 7.7GB.
Then I navigated to ‘/ad00/orabase/admin/scripts/exports’ and checked there was a directory called ‘exp_gems_logs’. As GEMS is quite small, and the customer wants a ‘daytime’ export so not to interfere with the evening backups, I ran the export at the command line with ‘./ exp_gems.sh’.
Thirty minutes later the request was complete.
Within UNIX you can ‘echo $ORACLE_SID’, or from sqlplus use ‘select instance_name from v$instance;’.
As part of a project to reduce space within a cloned database it was decided that a large table could be excluded from the regular scripted copy.
I needed to extract the tables definition from the master, to create an empty version on the clone after the copy was complete.
Being Oracle version 9.2.0.9, I didn’t have to use the ‘exp’ command (the output of which needs a lot of tidying), but could instead use the <b>’get_ddl'</b> function of the <b>’dbms_metadata'</b> package – like so …
— Script to export the definition of table PROC_FIL and its index.
— Remember to add semi-colons, amend ‘storage (initial 1k next 1k minextents
— 1 pctincrease 0 freelists 1 freelist groups 1 buffer_pool default);
— for example, and fix broken lines.
set heading off;set echo off;
set pages 999;
set long 90000;
spool ddl_pf.sqlselect dbms_metadata.get_ddl(‘TABLE’,’PROC_FIL’,’KIS’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’PF_PK’,’KIS’) from dual;
spool off;
After a while using HPOV over a slow network the application may freezes when I try to perform any action that spawnes a new window (eg: ‘Closure Code’).
The cure is to re-install, but its not too difficult.
1) take copies of srv.DAT and Views.DAT from ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’
2) delete the folder ‘C:Documents and Settingsrichard smithApplication DataHewlett-PackardOpenViewService Desk’.
3) start up hpov and click ‘next’ within the connection wizard
4) paste the server name from srv.DAT (eg: ’nta-hpsd02.steria.co.uk:30999′), into the text-box.
5) type name and password (eg: ‘Richard Smith’, ‘password’).
6) type anything for the friendly name (eg: ‘hpov’), then click finish.
7) before loging-in copy the Views.DAT back into the folder deleted in step-2
8) login as normal (there will be an unusually long delay first time)
How to change an Oracle password temporarily?
In Oracle it is possible to change a password temporarily. This can be useful for DBAs who wish to act as a different user.
SQL> select username,password from dba_users where username='SCOTT';
USERNAME PASSWORD
-------- ----------------
SCOTT F894844C34402B67
Save the hash of the password somewhere safe. Then change it …
SQL> alter user scott identified by tmppassword;
Login with the credentials: scott/tmppassword, then after doing your work change the password back by using an undocumented feature called “by values”
SQL> alter user scott identified by values 'F894844C34402B67';
Similar to this page where I manually extended a tablespace that had only 1% free.
But this time I needed to create a new datafile as all the current datafile’s were at there max (2GB for this customer).
1) Confirmed tablespace ‘I_Large5’ has only 1% free.
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 3
I_LARGE5 56929280 56142848 786432 1
2) I listed the I_LARGE5 datafile’s to see how large the current ones are, and to get the path and name of the latest one.
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 = 'I_LARGE5'
order by file_name;
3) looked in the path to make sure there was enough space for a new 1GB file.
df -k /db01/oradata/CIX/
4) Then created a new datafile, incrementing the name by one …
alter tablespace I_LARGE5 add datafile '/db01/oradata/CIX/i_large5_14.dbf' size 500m;
5) lastly, as the datafiles of this database are copied to a standby server every night I needed to amend the copy scripts to include this newly created datafile.
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)
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
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
/
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).
“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.
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 