Monday, February 29, 2016

ORA-27492: unable to run job : scheduler unavailable

Check JOB_QUEUE_PROCESSES parameter value.

User was able to execute the job manually by selecting "Use Current Session" option within TOAD. But gets an error ORA-27492 when choosing "Do not use current session" option.

Alter session set job_queue_processes = 10; (based on #of jobs you will be executing simultaneously)

ORA-27492: unable to run job "JOB_NAME": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 2

Monday, November 16, 2015

Case insensitive search...

Well, I know this is not a new feature and this was exists from 10g but I just came across with this and wanted to record in my repository.

We know that there are functional indexes available for searching data in either UPPER or LOWER case searches.

But one can still achieve this case insensitive search by changing NLS parameters as demonstrated below.

Please note that this will not work for LIKE operator. 

SQL> show parameter nls_comp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY
SQL> show parameter nls_sort

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
SQL>

SQL> create table san_ci_test (user_name varchar2(30));

Table created.

SQL>
SQL> insert into san_ci_test values ('santhosh');

1 row created.

SQL> insert into san_ci_test values ('Santhosh');

1 row created.

SQL> insert into san_ci_test values ('SANTHOSH');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select *from san_ci_test where user_name = 'santhosh';

USER_NAME
------------------------------
santhosh

As you see there is only one row selected above.

Now change the NLS parameters and check the same query results.

SQL> alter session set nls_comp = ansi;

Session altered.

SQL> alter session set nls_sort = binary_ci; (ci - Case Insensitive)

Session altered.

SQL> select * from san_ci_test where user_name = 'santhosh';

USER_NAME
------------------------------
santhosh
Santhosh
SANTHOSH

SQL>

Yes these parameters can be set at database/instance level but I definitely wouldn't recommend that. 

Friday, October 2, 2015

Adding column with default value on a large table...

We all know that we can easily add a column to an existing table with DEFAULT value. It's not a problem when the the table size is small because when adding a column with default value, all existing rows needs to be updated with the value specified in default clause.

Problem is only when the table has millions of records. It will take anywhere from minutes to close to hours (depending on the size of the table).

Thanks to 11g with its new feature:

Alter Script:

alter table table_name add column_name varchar2(3) default 'YES' NOT NULL;

The above statement will not issue an update to existing records. New records will have the value YES. So, what will happen to existing records data for this column? Here comes the 11g new feature, when a user selects the column for an existing record, oracle gets the default value from the data dictionary.

Now you can happily add a column with default value to even billion records table in fraction of a second!!! sweet...

Here is the test results with and without NOT NULL:

15:59:35 SQL> select count(1)from ln_stage_data;

  COUNT(1)
----------
  12540411

Elapsed: 00:00:00.93
15:59:40 SQL> alter table ln_stage_data add test_flag char(1) default 'Y';

Table altered.

Elapsed: 01:02:03.38
17:01:51 SQL> alter table ln_stage_data add test_flag2 char(1) default 'Y' not null;

Table altered.

Elapsed: 00:00:01.13
17:02:14 SQL>