17.047762
80.098187
Oracle 11g On cent OS
Posted by Sriram Sanka on October 9, 2010
Posted in Oracle videos | Leave a Comment »
Create Oracle Database using ASM manually via scripts
Posted by Sriram Sanka on May 12, 2010
Posted in Oracle videos | Leave a Comment »
Not null constraint :–ORA-00932: inconsistent datatypes: expected NUMBER got LONG
Posted by Sriram Sanka on December 9, 2009
SQL> sho user
USER is "SCOTT"
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;
9
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%';
CONSTRAINT_NAME
------------------------------
SYS_C006082
SYS_C006081
SYS_C006080
SYS_C005558
SYS_C005696
SYS_C005695
6 rows selected.
SQL>
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;
REVERSE('SRIRAMOR
-----------------
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.
SQL>
sriram.
Source : http://www.oracle.com/code 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
Connected.
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
Connected.
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>
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>
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.
SQL> GRANT PLUSTRACE TO raghu;
Grant succeeded.
SQL> conn raghu/raghu
Connected.
SQL> set autotrace on
SQL> select sysdate from dual;
SYSDATE
---------
04-DEC-09
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 |
-----------------------------------------------------------------
Statistics
----------------------------------------------------------
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
SQL>
Posted in Oracle Server Administration | 1 Comment »
Not Just Databases 