My Experiences with Databases & More

Oracle-MySQL-SQL SERVER-Python-Azure-AWS-Oracle Cloud-GCP etc

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Total Views

    • 558,511 hits
  • $riram $anka

    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

Archive for December, 2009

Not null constraint :–ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Posted by Sriram Sanka on December 9, 2009

SQL> sho user
SQL> select constraint_name from user_constraints
2 where search_condition not like '%NOT NULL';
where search_condition not like '%NOT NULL'
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

SQL> create or replace function get_search_condition(p_cons_name in varchar2 ) return varchar2
2 authid current_user
3 is
4 l_search_condition user_constraints.search_condition%type;
5 begin
6 select search_condition into l_search_condition
7 from user_constraints
8 where constraint_name = p_cons_name;
10 return l_search_condition;
11 end;
12 /

Function created.

SQL> select constraint_name
2 from user_constraints
3 where get_search_condition(constraint_name) like '%NOT NULL%';


6 rows selected.


Source:- Asktom`s get_search_condition function.


Posted in Uncategorized | 1 Comment »

Reverse function in oracle

Posted by Sriram Sanka on December 9, 2009

SQL> select reverse('sriram oracle DBA') from dual;

ABD elcaro marirs

As this reverse funcction is undocumented we can achive the same using a procedure........

SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /

Procedure created.

SQL> exec rev('sriram oracle DBA');
ABD elcaro marirs

PL/SQL procedure successfully completed.



Source : tips

Posted in Functions In oracle | Leave a Comment »

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

Posted by Sriram Sanka on December 4, 2009

SQL> conn raghu/raghu
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

SQL> conn / as sysdba
SQL> grant plustrace to raghu;
grant plustrace to raghu
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist.

SQL> @ %oracle_home%\sqlplus\admin\plustrce.sql;
SQL> drop role plustrace;
drop role plustrace
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist

SQL> create role plustrace;

Role created.

SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$session to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.


Grant succeeded.
SQL> conn raghu/raghu

SQL> set autotrace on
SQL> select sysdate from dual;


Execution Plan
Plan hash value: 1388734953

| Id | Operation | Name | Rows | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |

0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Posted in Oracle Server Administration | 1 Comment »

Tales From A Lazy Fat DBA

Its all about Databases & their performance, troubleshooting & much more .... ¯\_(ツ)_/¯

Thinking Out Loud

Michael T. Dinh, Oracle DBA

Notes On Oracle

by Mehmet Eser

Oracle Diagnostician

Performance troubleshooting as exact science


get sum oracle stuffs

Data Warehousing with Oracle

Dani Schnider's Blog


Oracle DBA's Daily Work

Welcome everyone!! The idea of this blog is to help the DBA in their daily tasks. Enjoy.

Anand's Data Stories

Learn. Share. Repeat.

Tanel Poder's blog: Core IT for geeks and pros

Oracle Performance Tuning, Troubleshooting, Internals

Yet Another OCM

Journey as an Oracle Certified Master

Sharing Knowledge is ultimate key to Gaining knowledge...

Neil Chandler's DB Blog

A resource for Database Professionals

DBA Kevlar

Tips, tricks, (and maybe a few rants) so more DBA's become bulletproof!

OraExpert Academy

Consulting and Training

%d bloggers like this: