Author: C. Peter Chen

To do so, create this following two functions. The first function tests for numbers, and the second tests for dates. You should be sure to change the schema name to the correct value for your environment.

create or replace function schema1.is_number(in_var in varchar2)
return varchar2
is
  v_number number;
begin
  select to_number(in_var) into v_number from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

create or replace function schema1.is_date(in_var in varchar2, in_format in varchar2)
return varchar2
is
  v_date date;
begin
  select to_date(in_var, in_format) into v_date from dual;
  return 'Y'; -- No exception, so is a number
exception
  when others then
  return 'N'; -- is not a number
end;

Below are two samples on how to use our newly created is_number() function.

select schema1.is_number('pete') from dual;
select schema1.is_number(123) from dual;

The first query will result “N” because “pete” is not a number. The second query will return “Y”.

Below are four samples on how to use our newly created is_date() function.

select schema1.is_date('pete','mm/dd/yyyy') from dual;
select schema1.is_date('2-MAY-2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/99/2008','mm/dd/yyyy') from dual;
select schema1.is_date('05/02/2008','mm/dd/yyyy') from dual;

The first query will result “N” because “pete” is not a date. The second also returns “N” because the date entered does not conform to the format specified. The third also will return “N” because the date, although formatted correctly, is not a valid date. Finally, we will have “Y” for the last test.

The example below retrieves a sample list of employees, their job roles, and how many hours they have billed to a particular customer.

select e.emp_name, e.job_role, sum(b.hours) from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)
--------- ------------- ------------
Sandy     attorney      10
Bob       programmer    4
Jill      programmer    8
John      accountant    32
Barbara   secretary     20
Danny     proj manager  8

The company has a simple billing rule. Attorney hours are billed at $200 per hour, programmers at $100 per hour, accountants at $100 per hour, and all others at $25 per hour. We can use the case statement to help us derive how much to bill the customer.

select e.emp_name, e.job_role, sum(b.hours),  
case when job_role='attorney' then sum(b.hours)*200
  when job_role='programmer' then sum(b.hours)*100
  when job_role='accountant' then sum(b.hours)*100
  else charge=sum(b.hours)*25
end as charge
from employees e, billing b where e.emp_id=b.emp_id and b.customer_id=126 group by e.emp_name, e.job_role;

emp_name  job_role      sum(b.hours)  charge
--------- ------------- ------------- -------
Sandy     attorney      10            200
Bob       programmer    4             400
Jill      programmer    8             800
John      accountant    32            3200
Barbara   secretary     20            500
Danny     proj manager  8             200

Of course, this is just a hands-on illustration of the case expression in Oracle SQL. In real life, it is arguable that this type of business logic should probably be written elsewhere, perhaps with the base rates stored in a table, so that it can be easily modified if need be.

Often, we store data in a normalized way in our database, perhaps something that resembles key-value pairs. To illustrate, let us see the following table, which describes properties of computers.

select comp_id, comp_property, comp_property_value from schema1.comp_properties;


COMP_ID COMP_PROPERTY COMP_PROPERTY_VALUE
------- ------------- -------------------
1       hostname      comp1
1       ipaddress     192.168.0.1
1       os            Windows XP SP2
1       hdd1          60gb
1       hdd2          500gb
2       hostname      comp2
2       ipaddress     192.168.0.2
2       os            MacOS 10.5
2       hdd1          80gb
3       hostname      comp3
3       os            Ubuntu Linux 7.10

In this particular case, we can see that the three computers have different properties. While in this example of three computers we can see the information easily, we can also imagine that it will become cumbersome if we are managing 100 computers instead of only three. Thus, a pivot table type of presentation would be much more useful.

To rewrite the SQL statement to present the same data in a pivot table manner, we can do the following.

create or replace schema1.v_comp_properties
select comp_id, hostname, ipaddress, os, hdd1, hdd2
from (
	select comp_id,
	max(case when comp_property='hostname' then comp_property_value else null end) as hostname,
	max(case when comp_property='ipaddress' then comp_property_value else null end) as ipaddress,
	max(case when comp_property='os' then comp_property_value else null end) as os,
	max(case when comp_property='hdd1' then comp_property_value else null end) as hdd1,
	max(case when comp_property='hdd2' then comp_property_value else null end) as hdd2
	from schema1.comp_properties
	group by comp_id
);

The result should look like the following.

select comp_id, hostname, ipaddress, os, hdd1, hdd2 from schema1.v_comp_properties;

COMP_ID HOSTNAME IPADDRESS   OS                HDD1 HDD2
------- -------- ----------- ----------------- ---- -----
1       comp1    192.168.0.1 Windows XP SP2    60gb 500gb
2       comp2    192.168.0.2 MacOS 10.5        80gb
3       comp3    192.168.0.3 Ubuntu Linux 7.10

With this pivot table, we will be able to see all properties of each computer on each row easily, while still maintain a normalized database design.

The following example assumes you have two databases, db1 and db2. You are currently logged into db1 and need a link to db2. We also assume the entry for db2 has already been established in the server’s TNSNAMES.ORA file.

-- Fixed user method
create database link db2
connect to scott
identified by tiger 
using 'db2';

-- Connected user method
create database link db2
using 'db2';

-- Current user method
create database link db2
connect to current_user 
using 'db2';

The difference between the three methods above is user authentication. In the fixed user example, regardless of who you are in db1, you will always access db2 as the user “scott”. With the connected user method, the link will always connect to db2 as whoever created the link; naturally, with this method, you will need to make sure you have a valid account in db2 as well. With the current user method, the link will always connect to db2 as whoever is using the link; this is arguably the most secure method.

If you need the link to be seen by every user, you may consider a public link:

create public database link db2
using 'db2';

With the database link established, you are now able to perform DML queries in db2 from db1. Here’s an example:

select a.name, b.telephone
from myschema.user_list a, scott.address_book@db2 b
where a.name=b.name;

Note the usage of “@db2” in the above example which signifies the usage of the database link.

One thing to keep in mind when establishing database links is security, thus, make sure you understand the potential risks of your design before implementing it. Fixed user method is the most commonly used one, and the fixed user typically ends up having more privileges in the remote database than necessary, therefore allowing users in db1 to do more things in db2 than necessary. Another concern is with how Oracle stores fixed user passwords — they are stored in the SYS.LINK$ table in clear text!

Checking for any down data files:

select file_name, tablespace_name, status, bytes/1000000 as mb, maxbytes/1000000 as mb_max 
from dba_data_files 
where status<>'AVAILABLE';

Checking for any down control files:

select name, status
from v$controlfile 
where status is not null;

Checking for any down tablespaces:

select tablespace_name, status, contents, extent_management
from dba_tablespaces
where status<>'ONLINE';

Checking to see if a tablespace is getting close to max size (less than 10% free):

select total.name as tblspace,
  nvl(free_space, 0) as free,
  nvl(total_space-free_space, 0) as used,
  total_space,
  round(nvl(free_space,0)/total_space,2)*100 as pct_free
from 
  (
    select tablespace_name, sum(bytes/1024/1024) as free_Space 
    from sys.dba_free_space 
    group by tablespace_name
  ) free, 
  (
    select b.name, sum(bytes/1024/1024) as total_space
    from sys.v_$datafile a, sys.v_$tablespace b 
    where a.ts# = b.ts#
    group by b.name
  ) total 
where free.tablespace_name(+) = total.name 
and total.name='TABLESPACE_NAME_HERE' 
and round(nvl(free_space, 0)/total_space,2)<=0.10;

Suspicious of someone stealing your system passwords and logging in remotely? Use this to check for system logins from machines you do not expect.

select username, osuser, program, machine 
from sys.v_$session 
where upper(machine) in('LIST_VALID_MACHINES_HERE','DBA_PC','BOSS_PC') 
and upper(username) in('SYS','SYSTEM');

This select statement can be run by someone with DBA privileges (as it makes use of v$ views) to list who is locking what.

select a.session_id,
  c.serial#,
  a.oracle_username,
  a.os_user_name,
  c.machine,
  a.locked_mode,
  b.object_name,
  b.object_type
from v$locked_object a,dba_objects b, v$session c
where a.object_id = b.object_id
  and a.session_id = c.sid;

The query below is more specific. With it, you will be able to identify exactly which session is block another session, if any such conditions exist.

select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' as blocking
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.block=1 and l2.request > 0
and l1.id1 = l2.id1 and l2.id2 = l2.id2;

If necessary, we can use the following example to kill a particular session. It may be useful if the locking session is actually from a hanging process, for example, though typically we should have the user release his/her lock gracefully.

alter system kill session '123,5555' immediate;

Note that the “123” above should be replaced by the session ID, and the “5555” should be replaced by the serial #. Those two values are shown in the first two fields in the select SQL statement above.