Some Good Triggers

Trigger to detect SQL Injection Attacks  
Problem with Trigger

Overlap,Explain Plan

Overlap

If any of the condition in where clause of a query overlaps , with the parameter in overlap function query returns value else not

select 'a' from dual where (sysdate,sysdate+1) overlaps (sysdate,sysdate+1);

---------------------------------------------
SQL> set autotrace on explain

Then execute the SQL script that you want to get the explain plan

--------------------------------------------------------------------------------------------------------
EXPLAIN PLAN
------------------
EXPLAIN PLAN statement determine the execution plan Oracle Database follows to execute a specified
SQL statement.


TO INSERT INTO PLAN TABLE

EXPLAIN PLAN
SET STATEMENT_ID = 'Raise in Tokyo'
INTO plan_table FOR SELECT * FROM DUAL;

DISPLAYING  PLAN_TABLE OUTPUT

select * from plan_table t
----------------------------------------------

select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 ) "Operation",object_name
"Object",position, BYTES,CARDINALITY,COST from plan_table start with id = 0
connect by prior id=parent_id;
---------------------------------------
select * from table(dbms_xplan.display);
-----------------------------------------

SELECT  *  FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE',NULL,'BASIC'));

BASIC :   Display only the minimum set of information

TYPICAL: This is the default. Display most information  of the explain plan (operation id, name and option,#rows, #bytes and optimizer cost).

ALL: Maximum user level, like typical with additional informations (PROJECTION, ALIAS and information about REMOTE SQL if the operation is distributed).

Some words about PLAN_TABLE resides in sys user with name PLAN_TABLE$ ;which is a  ON COMMIT PRESERVE ROWS GLOBAL TEMPORARY TABLE
 
------------------------------------
How to Read Explain Plan
------------------------------------
select * from sys.v_$sql_workarea t
--------------------------------------
select * from sys.v_$sql_plan t
-------------------------------

Encryption

CREATE OR REPLACE FUNCTION fnc_encryption(pi_src IN VARCHAR2) RETURN CLOB
IS
v_enc_val raw(32767);
v_key1 VARCHAR2(16) := '1111111111111111';
v_key2 VARCHAR2(16) := '2222222222222222';
v_charterset VARCHAR2(8) := 'AL32UTF8';
v_mod number := DBMS_CRYPTO.ENCRYPT_AES128
+ DBMS_CRYPTO.CHAIN_CBC
+ DBMS_CRYPTO.PAD_PKCS5;
BEGIN
v_enc_val := DBMS_CRYPTO.ENCRYPT(
src => UTL_I18N.STRING_TO_RAW(pi_src, v_charterset),
typ => v_mod,
key => UTL_I18N.STRING_TO_RAW(v_key1, v_charterset),
iv => UTL_I18N.STRING_TO_RAW(v_key2, v_charterset)
);
RETURN RAWTOHEX(v_enc_val);
END fnc_encryption;
/



Scenario 1: Use the function to encrypt all of the rows in the table and then repeat the operation - no error occurs:

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

SQL> update TEST
2 SET NAME=fnc_encryption(name);

3 rows updated.

Time ; Avoid duplicate

select DBMS_UTILITY.GET_TIME from dual;

select * from v$timer;

----------------------------------------

Avoid duplicate

To avoid duplicate rows there are three steps

1) Use DISTINCT Keyword
2) Use UNIQUE Keyword
3) Use GROUP BY Keyword

Examples

1) Select distinct vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
2) Select unique vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast;
3) Select vcname,empcode,deptcode,addr,age,phoneno from hr_emp_dup_mast
group by vcname,empcode,deptcode,addr,age,phoneno;

Level queries

To get the higher manager of a particular employee


select yy.empno,yy.mgr,yy.ename,rt.empno,rt.mgr,rt.ename from
(select * from emp t
connect by prior t.empno=t.mgr start with mgr is null) yy,
emp rt
where yy.empno=rt.mgr --and rt.empno=7782

-------------------------------------------------------------------------------

To get repeating data

select deptno,dname,loc,ltrim(ename,' ,') ename,ltrim(hiredate,' ,') hiredate,ltrim(mgr,' ,') mgr
,ltrim(job,' ,') job
from (
select deptno,dname,loc,SYS_CONNECT_BY_PATH (ename,' ,') ename,SYS_CONNECT_BY_PATH (hiredate,' ,') hiredate,
SYS_CONNECT_BY_PATH (job,' ,') job,SYS_CONNECT_BY_PATH (mgr,' ,') mgr
from (
select r.deptno,r.dname,r.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm ,
row_number() over (partition by r.deptno order by r.deptno) r,
row_number() over (partition by r.deptno order by r.deptno) -1 c
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
) start with r = 1
connect by c = prior r
order by rownum desc)
where rownum = 1 ;


OR


select r.deptno,r.dname,r.loc,wmsys.wm_concat(e.ename),wmsys.wm_concat(e.job),wmsys.wm_concat(e.mgr),wmsys.wm_concat(e.hiredate),wmsys.wm_concat(e.sal),wmsys.wm_concat(e.comm)
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
group by r.deptno,r.dname,r.loc

References

1) www.orafaq.com/wiki/Main_Page

2) www.orafaq.com/wiki/Scripts

3) www.orafaq.com/wiki/SQL_FAQ

OS-Version

How to Identify OS or Oracle 64 bit or 32 bit

select * from v$version;

---------------------------------------------------------------------------

MOVE

Re: alter table move

Processing the code


select * from tab where P1 = 110

last a few seconds. After the code


alter table tab enable row movement
alter table tab move

the select-code above last about an hour! I thought this might have to do with the index (does the move-statement affect the index?), but when I run the code


create index Ix_P1 on tab (P1)

I get the ORA-00955: name is already used by an existing object.


Re: alter table move


When you do alter table .. move all the indexes goes into unusable state.
You need to rebuild all the indexes of the table after issuing alter table move commands....

SQL> create table dummy(x int);
Table created.

SQL> create index dummy_idx on dummy(x);

Index created.

SQL> insert into dummy values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> alter table dummy move;

Table altered.

SQL> select index_name,status from user_indexes where table_name = 'DUMMY';

INDEX_NAME STATUS
------------------------------ --------
DUMMY_IDX UNUSABLE

Alphabets,Level, Analytic Query

Alphabets

WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;

------------------------------------------------------------------------
Some Analytic Functions

SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;

SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;

SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;

---------------------------------------------------------------------------
Level Query


select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode

Calender,Dates,Objects created

Calender of a year


select lpad( Month, 20-(20-length(month))/2 ) month
, "Su" , "Mo" , "Tu" , "We" , "Th" , "Fr" , "Sa"
from ( select to_char(dt,'fmMonthfm YYYY') month
, to_char(dt+1,'iw') week
, max(decode(to_char(dt,'d'),'1',lpad(to_char(dt,'fmdd'),2))) "Su"
, max(decode(to_char(dt,'d'),'2',lpad(to_char(dt,'fmdd'),2))) "Mo"
, max(decode(to_char(dt,'d'),'3',lpad(to_char(dt,'fmdd'),2))) "Tu"
, max(decode(to_char(dt,'d'),'4',lpad(to_char(dt,'fmdd'),2))) "We"
, max(decode(to_char(dt,'d'),'5',lpad(to_char(dt,'fmdd'),2))) "Th"
, max(decode(to_char(dt,'d'),'6',lpad(to_char(dt,'fmdd'),2))) "Fr"
, max(decode(to_char(dt,'d'),'7',lpad(to_char(dt,'fmdd'),2))) "Sa"
from ( select trunc(sysdate,'y')-1+rownum dt
from all_objects
where rownum <= add_months(trunc(sysdate,'y'),12)
-
trunc(sysdate,'y')
)
group by to_char(dt,'fmMonthfm YYYY')
, to_char( dt+1, 'iw' )
)
order by to_date( month, 'Month YYYY' )
, to_number(week)

--------------------------------------------------
To get all Dates in a Year

select mydate,to_char(mydate,'Day') from(
select (level-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from dual
connect by level <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy')-to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

OR


select mydate,to_char(mydate,'Day') from(
select (rownum-1)+to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') mydate from all_objects
where
rownum <=to_date('31-12-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') - to_date('01-01-'||to_char(sysdate,'yyyy'),'dd-mm-yyyy') +1)

The above query assumes that all_objects returns at least 365 records.

----------------------------------------------------------------------------
Number of objects created in a month of year

select to_char(trunc(created,'MONTH'),'YYYY-MON') "Month", count(*) "Nb"
from user_objects group by trunc(created,'MONTH')
order by trunc(created,'MONTH');

Lock,Month,Week Query

Query to identify Locked Tables

SELECT e.*, (select terminal from v$session r where e.SID = r.SID) terminal,
(select name from sys.obj$ where obj# = e.ID1) objname
FROM sys.v_$lock e

-----------------------------------------------------------------
To get all months in a year

select to_char(add_months(TRUNC(SYSDATE,'Y'), level-1), 'Month') from dual
connect by level <= months_between(sysdate, TRUNC(SYSDATE,'Y'))+1
order by add_months(TRUNC(SYSDATE,'Y'), level-1);

select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val from dual connect by rownum <= 12;

-----------------------------------------------------------------
To get Weeks in a year

select trunc(dte,'W') week_start,trunc(dte,'W')+6 week_end,to_char(dte,'Month') Mnth
,to_char(dte,'YYYY') Yr
from (select trunc(sysdate,'YYYY')+(7*(level-1)) dte
from dual
connect by level <= ceil((add_months(trunc(sysdate,'YYYY'),12)-trunc(sysdate,'YYYY'
))/7));
-----------------------------------------------------------------