Does Oracle 11g support CREATE ASSERTION?
Database SQL Language Reference

There is no CREATE ASSERTION in Oracle. RDBMS products do not necessarily implement all features in the SQL standards.

And to add to the above:

Check Constraints


  Restrictions on Check Constraints 
  
  The condition of a check constraint can refer to any column in the
  table, but it cannot refer to columns of other tables.
  
  Conditions of check constraints cannot contain the following constructs:
  
  Subqueries and scalar subquery expressions