How to check cluster name

Just a quick short post in the late hours ( :) ) on how to check the cluster name.

You can find out the cluster name with $ORA_CRS_HOME/bin/cemutlo -n

[grid@oel-ocm-pc2 ~]$ . oraenv
The Oracle base remains unchanged with value /u01/app/grid
[grid@oel-ocm-pc2 ~]$
[grid@oel-ocm-pc2 ~]$ cemutlo -n
[grid@oel-ocm-pc2 ~]$

Further, if you want to check the clusterware version installed,
run $ORA_CRS_HOME/bin/crsctl query crs softwareversion

[grid@oel-ocm-pc2 ~]$ crsctl query crs softwareversion
Oracle Clusterware version on node [oel-ocm-pc2] is []
[grid@oel-ocm-pc2 ~]$



PL/SQL Function Result Cache

As you may already know result cache represents nice feature which is used to store results of query execution in the result cache area  (part of the SGA).

From 11g you can also cache pl/sql function results in the SGA and its available to all sessions which runs the application.
It’s easy to use, what you need to do is just mark the pl/sql function that its results should be cached.

You enable result caching for a function by adding the RESULT_CACHE clause in the function definition.
Also you can specify the optionally RELIES_ON clause which specifies tables/views on which the function results depends – since in this example the results depend on the HR.EMPLOYEES table I’ve added the optional keyword RELIES_ON

SQL> create function getEmpSalary(p_email varchar2)
  2  return number
  3  result_cache relies_on (employees)
  4  is
  5  v_salary employees.salary%type;
  6  begin
  7  select salary into v_salary from employees where lower(email) = lower(p_email);
  8  return v_salary;
  9  end;
 10  /

Function created.

Since this is test environment I flushed the cache and confirm that there are no cached information by querying the V$RESULT_CACHE_OBJECTS dictionary view. Continue reading →

Recreate DUAL table

Just a little experiment with the DUAL table.
Someone might find it useful.

[oracle@rac1edu ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Mon Jul 21 08:21:59 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from dual;


SQL> desc dual;
 Name Null? Type
 ----------------------------------------- -------- ----------------------------

SQL> drop table dual;

Table dropped.

SQL> select * from dual;
select * from dual
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 849530880 bytes
Fixed Size 1348244 bytes
Variable Size 524291436 bytes
Database Buffers 318767104 bytes
Redo Buffers 5124096 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01775: looping chain of synonyms
Process ID: 27181
Session ID: 129 Serial number: 3


Continue reading →

Daily baseline template from sqlplus

If you want to create repeating baseline template from sqlplus you might struggle if you want to set the frequency to daily (every day in the week).
This is because in the documentation it is not specified what you should specify for the day_of_week parameter of the dbms_workload_repository.crete_baseline_template function.

What is specified is this:

Parameter: day_of_week
Description: Day of week that the baseline should repeat on. Specify one of the following values: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

If you want the baseline for specific day, then great just specify one of the available values.

But if you want day_of_week to be set to daily then you’ll have to specify ALL

                                                   hour_in_day => 10,
                                                   duration    => 6,
                                                   start_time  => next_day(sysdate,'MONDAY'),
                                                   end_time    => to_date('01012015','ddmmyyyy'),
                                                   baseline_name_prefix  => 'TEST_',
                                                   template_name => 'TEMPLATE_FOR_TESTING',
                                                   expiration   => NULL,
                                                   dbid         => null);
PL/SQL procedure successfully completed.