Pages
▼
Wednesday, February 28, 2018
Tuesday, February 20, 2018
Monday, February 19, 2018
Sunday, February 18, 2018
Saturday, February 17, 2018
Friday, February 16, 2018
Tuesday, February 13, 2018
Monday, February 12, 2018
Saturday, February 10, 2018
Friday, February 9, 2018
Thursday, February 8, 2018
How To Delete Duplicate Records in SQL Oracle
select rowid,empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;
Using self-join with ROWID
=========================
delete FROM emp a
WHERE rowid NOT IN
(SELECT max(rowid) FROM emp b WHERE a.empno = b.empno );
(OR)
select rowid,empno,ename FROM emp a
WHERE rowid NOT IN
(SELECT min(rowid) FROM emp b WHERE a.empno = b.empno );
rollback;
Using rowid with GROUP BY Clause
================================
delete FROM emp
WHERE rowid not IN (SELECT min(rowid) FROM emp GROUP BY empno);
(OR)
select * FROM emp
WHERE rowid NOT IN (SELECT MAX(rowid) FROM emp GROUP BY empno);
rollback;
Using rowid with row_number()
===============================
select * FROM emp
WHERE rowid IN
(SELECT delrowid FROM
(SELECT rowid delrowid,row_number() over(partition BY empno order by empno) rownm
FROM emp )
WHERE rownm > 1 );
Using rowid with dense_rank()
=============================
delete FROM emp
WHERE rowid IN (SELECT delrowid
FROM
(SELECT rowid delrowid, dense_rank() over(partition BY empno order by rowid) rownm
FROM emp )
WHERE rownm > 1 );
rollback;
Using Group By
===============
select * FROM emp
WHERE (empno,ename,sal) IN
(SELECT empno,ename,sal FROM emp GROUP BY empno,ename,sal having count(empno)>1 );
rollback;
sub partitioning in oracle or composite partitioning in Oracle RANGE-LI...
******************************************************************************/
drop table EMP_COMP_RH;
CREATE TABLE EMP_COMP_RH
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
SUBPARTITION BY HASH (EMPNO) SUBPARTITIONS 4
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy')),
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy')),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy')),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
)ENABLE ROW MOVEMENT;;
INSERT INTO EMP_COMP_RH select * from EMP;
commit;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RH');
END;
/
select * from EMP_COMP_RH;
drop table EMP_COMP_RL;
CREATE TABLE EMP_COMP_RL
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
SUBPARTITION BY LIST(DEPTNO)
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy'))
(
SUBPARTITION SP1 VALUES ('10'),
SUBPARTITION SP2 VALUES ('20'),
SUBPARTITION SP3 VALUES ('30'),
SUBPARTITION SP4 VALUES ('40')
) ,
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy'))
(
SUBPARTITION SP11 VALUES ('10'),
SUBPARTITION SP21 VALUES ('20'),
SUBPARTITION SP31 VALUES ('30'),
SUBPARTITION SP41 VALUES ('40')
),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy'))
(
SUBPARTITION SP12 VALUES ('10'),
SUBPARTITION SP22 VALUES ('20'),
SUBPARTITION SP32 VALUES ('30'),
SUBPARTITION SP42 VALUES ('40')
),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
(
SUBPARTITION SP13 VALUES ('10'),
SUBPARTITION SP23 VALUES ('20'),
SUBPARTITION SP33 VALUES ('30'),
SUBPARTITION SP43 VALUES ('40')
)
)ENABLE ROW MOVEMENT;
INSERT INTO EMP_COMP_RL select * from EMP;
commit;
select * from EMP_COMP_RL;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RL');
END;
/
What is a Hash Partition in Oracle
/* =========================================================================*/
DROP TABLE EMP_HASH;
CREATE TABLE EMP_HASH
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY HASH ( EMPNO )
PARTITIONS 4
ENABLE ROW MOVEMENT;
insert into EMP_HASH select * from EMP;
commit;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_HASH');
END;
/
select * from EMP_HASH where empno=7369;
What is a LIST Partition in Oracle
/* ==========================================================================*/
DROP TABLE EMP_LIST;
CREATE TABLE EMP_LIST
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO)
(
PARTITION P1 VALUES ('10'),
PARTITION P2 VALUES ('20'),
PARTITION P3 VALUES ('30'),
PARTITION P4 VALUES ('40')
) ENABLE ROW MOVEMENT;
INSERT INTO EMP_LIST SELECT * FROM EMP;
COMMIT;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_LIST');
END;
/
select * from EMP_LIST where deptno=20;
select * from emp where deptno=10;
What is a Partition in Oracle and Type of Partitions , Range Partition
/*****************************************************************************
ORACLE PARTITIONS AND TYPES.
1) Range Partitioning
2) LIST Partitioning
3) HASH Partitioning
4) Composite RANGE-HASH Partitioning
5) Composite RANGE-LIST Partitioning
******************************************************************************/
drop table emp_part;
CREATE TABLE emp_part
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)PARTITION BY RANGE (sal)
INTERVAL (1000)
(
PARTITION p1 VALUES LESS THAN (1000) ,
PARTITION p2 VALUES LESS THAN (2000) ,
PARTITION p3 VALUES LESS THAN (3000) ,
PARTITION p4 VALUES LESS THAN (4000)
)ENABLE ROW MOVEMENT ;
select * from emp_part PARTITION(P2); --where sal>4000;
;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_PART');
END;
/
insert into emp_part select * from emp;
commit;
select * from emp_part PARTITION(p3);
drop table emp_RANGE_DT;
CREATE TABLE emp_RANGE_DT
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY RANGE (HIREDATE)
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy')),
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy')),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy')),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
) ENABLE ROW MOVEMENT;;
insert into emp_RANGE_DT select * from emp;
commit;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_RANGE_DT');
END;
/
/* ==========================================================================*/
DROP TABLE EMP_LIST;
CREATE TABLE EMP_LIST
(
EMPNO NUMBER(4,0) ,
ENAME VARCHAR2(10 ),
JOB VARCHAR2(9 ),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
)
PARTITION BY LIST (DEPTNO)
(
PARTITION P1 VALUES ('10'),
PARTITION P2 VALUES ('20'),
PARTITION P3 VALUES ('30'),
PARTITION P4 VALUES ('40')
) ENABLE ROW MOVEMENT;
INSERT INTO EMP_LIST SELECT * FROM EMP;
COMMIT;
BEGIN
DBMS_STATS.gather_table_stats('SDEV', 'EMP_LIST');
END;
/
select * from EMP_LIST where deptno=20;
select * from emp where deptno=10;
What is Global temporary Tables in Oracle
/*****************************************************************************
DECLARE GLOBAL TEMPORARY TABLE table-Name
{ column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ]
NOT LOGGED [ON ROLLBACK DELETE ROWS]
*****************************************************************************/
CREATE GLOBAL TEMPORARY TABLE students
( student_id numeric(10) NOT NULL,
student_name varchar2(50) NOT NULL,
student_address varchar2(50)
);
drop table GLOBAL_TEMP_TABLE ;
CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP_TABLE
( id NUMBER,
description VARCHAR2(20)
) ON COMMIT DELETE ROWS;
insert into GLOBAL_TEMP_TABLE values(1,'this is first row');
select * from GLOBAL_TEMP_TABLE;
commit;
drop table GLOBAL_TEMP PURGE;
CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP
(
id NUMBER,
description VARCHAR2(50)
)ON COMMIT PRESERVE ROWS;
commit;
insert into global_temp values(2,'this is ON COMMIT PRESERVE ROWS');
select * from global_temp;
Introduction To PLSQL Triggers and Types of Triggers
/*****************************************************************************
Oracle PLSQL TRIGGERS..
TYPES OF TRIGGERS
-----------------
1) DML Triggers
2) DDL Triggers
3) SYSTEM Triggers (STARTUP, SHUTDOWN) and user Evens (LOGON & LOGOFF)
4) INSTEAD OF Trigger
TYPES OF DML Triggers:
----------------------
ROW LEVEL & STATEMENT LEVEL TRIGGERS
=====================================
Firing Point : BEFORE
=========================
1) BEFORE INSERT TRIGGER
2) BEFORE UPDATE TRIGGER
3) BEFORE DELETE TRIGGER
Firing Point : AFTER
=====================
1) AFTER INSERT TRIGGER
2) AFTER UPDATE TRIGGER
3) AFTER DELETE TRIGGER
OLD & NEW Qualifiers.
-----------------------
INSERT : NEW
UPDATE : OLD & NEW
DELETE : OLD
Conditional Predicates
--------------------------
INSERTING
UPDATING
DELETING
*******************************************************************************/
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
[INSERT, UPDATE, DELETE [COLUMN NAME..]
ON table_name
Referencing [ OLD AS OLD | NEW AS NEW ]
FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]
DECLARE
[declaration_section
variable declarations;
constant declarations;
]
BEGIN
[executable_section
PL/SQL execute/subprogram body
]
EXCEPTION
[exception_section
PL/SQL Exception block
]
END;
********************************************************************************
/
CREATE or REPLACE TRIGGER EMP_TRIGGER
BEFORE
INSERT ON emp
FOR EACH ROW
BEGIN
:new.ename := upper(:new.ename);
END;
CREATE or REPLACE TRIGGER EMP_DELETE_TRIGGER
AFTER
DELETE ON emp
FOR EACH ROW
BEGIN
IF :old.empno = 7782 THEN
null;
raise_application_error(-20015, 'You cant delete this row');
END IF;
END;
delete from emp where empno=7782;
select * from mytable;
select * from emp;
CREATE or REPLACE TRIGGER EMP_DELETE_TRG_1
BEFORE
DELETE ON emp
FOR EACH ROW
BEGIN
insert into mytable values(:old.empno,sysdate);
END;
What is Conditional Predicates and Qualifiers in TRIGGERS PLSQL
CREATE OR REPLACE TRIGGER EMP_TRIGGER_PREDICATES
BEFORE INSERT OR DELETE OR UPDATE ON emp
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
BEGIN
/* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;
DBMS_OUTPUT.put_line(v_ChangeType ||'- '|| USER ||'-' ||SYSDATE);
END EMP_TRIGGER_PREDICATES;
/
PLSQL DML Triggers and Types of DML Triggers
/*****************************************************************************
Oracle PLSQL TRIGGERS..
TYPES OF TRIGGERS
-----------------
1) DML Triggers
2) DDL Triggers
3) SYSTEM Triggers (STARTUP, SHUTDOWN) and user Evens (LOGON & LOGOFF)
4) INSTEAD OF Trigger
TYPES OF DML Triggers:
----------------------
ROW LEVEL & STATEMENT LEVEL TRIGGERS
=====================================
Firing Point : BEFORE
=========================
1) BEFORE INSERT TRIGGER
2) BEFORE UPDATE TRIGGER
3) BEFORE DELETE TRIGGER
Firing Point : AFTER
=====================
1) AFTER INSERT TRIGGER
2) AFTER UPDATE TRIGGER
3) AFTER DELETE TRIGGER
OLD & NEW Qualifiers.
-----------------------
INSERT : NEW
UPDATE : OLD & NEW
DELETE : OLD
Conditional Predicates
--------------------------
INSERTING
UPDATING
DELETING
*******************************************************************************/
CREATE [OR REPLACE] TRIGGER trigger_name
BEFORE | AFTER
[INSERT, UPDATE, DELETE [COLUMN NAME..]
ON table_name
Referencing [ OLD AS OLD | NEW AS NEW ]
FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]
DECLARE
[declaration_section
variable declarations;
constant declarations;
]
BEGIN
[executable_section
PL/SQL execute/subprogram body
]
EXCEPTION
[exception_section
PL/SQL Exception block
]
END;
********************************************************************************
/
CREATE or REPLACE TRIGGER EMP_TRIGGER
BEFORE
INSERT ON emp
FOR EACH ROW
BEGIN
:new.ename := upper(:new.ename);
END;
CREATE or REPLACE TRIGGER EMP_DELETE_TRIGGER
AFTER
DELETE ON emp
FOR EACH ROW
BEGIN
IF :old.empno = 7782 THEN
null;
raise_application_error(-20015, 'You cant delete this row');
END IF;
END;
What is Instead of Trigger in PLSQL
/*****************************************************************************
INSTEAD OF TRIGGER -- To Update Complex Views...
******************************************************************************/
drop table employee;
CREATE TABLE employee
(
employee_id number(10) primary key,
employee_name varchar2(20),
location varchar2(20),
department_id number references departments(department_id)
);
DROP TABLE departments;
CREATE TABLE departments
(
department_id number(10) primary key,
department_name varchar2(30),
creation_date date default sysdate
);
CREATE OR REPLACE VIEW EMPLOYEE_DEPART_V AS
SELECT dept.department_id, dept.department_name,dept.creation_Date,
emp.employee_id,
emp.employee_name, emp.location
FROM DEPARTMENTS dept, EMPLOYEE emp
WHERE dept.department_id = emp.department_id;
insert into EMPLOYEE_DEPART_V values(1,'IT',SYSDATE,1,'RAVEENDRA','BANGALORE');
insert into EMPLOYEE_DEPART_V values(2,'SALES',SYSDATE,2,'RESHWANTH','HYDERABAD');
insert into EMPLOYEE_DEPART_V values(2,'IT',SYSDATE,2,'RAVEENDRA','BANGALORE');
commit;
delete from departments;
select * from EMPLOYEE_DEPART_V;
select * from employee;
select * from departments;
DROP TRIGGER EMP_DEPT_Instead_OF_TRG;
CREATE OR REPLACE TRIGGER EMP_DEPT_Instead_OF_TRG
INSTEAD OF INSERT ON EMPLOYEE_DEPART_V
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
INSERT INTO departments (department_id, department_name, creation_date)
VALUES (
:new.department_id,
:new.department_name,
:new.creation_date);
INSERT INTO employee
(employee_id,employee_name,location,DEPARTMENT_ID)
VALUES (:new.employee_id, :new.employee_name, :new.location,:new.DEPARTMENT_ID);
EXCEPTION
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate employee or department id');
END EMP_DEPT_Instead_OF_TRG;
PLSQL DDL Triggers with examples
/*****************************************************************************
DDL Triggers Examples..
CREATE OR REPLACE TRIGGER DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN
-----
END;
******************************************************************************/
CREATE TABLE DDL_TABLE_LOG
(
ora_dict_obj_name VARCHAR2(100),
ora_login_user VARCHAR2(100),
creation_date DATE,
ora_sysevent VARCHAR2(100),
ora_dict_obj_type VARCHAR2(100),
ora_dict_obj_owner VARCHAR2(100)
);
/
CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER AFTER DDL ON DATABASE
BEGIN
INSERT
INTO DDL_TABLE_LOG VALUES
(
ora_dict_obj_name ,
ora_login_user ,
sysdate ,
ora_sysevent ,
ora_dict_obj_type ,
ora_dict_obj_owner
);
END;
/
select * from DDL_TABLE_LOG;
truncate table DDL_TABLE_LOG;
LOGON & LOGOFF Triggers in PLSQL
/*****************************************************************************
USER EVENT LOGON & LOGOFF Triggers Examples..
CREATE OR REPLACE TRIGGER DDLTrigger_name
AFTER LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN
-----
END;
******************************************************************************/
DROP TABLE USER_EVENT_LOG;
CREATE TABLE USER_EVENT_LOG
(
ora_login_user VARCHAR2(100),
creation_date DATE,
ora_sysevent VARCHAR2(100)
);
/
CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER_LOGON AFTER LOGON ON DATABASE
BEGIN
INSERT
INTO USER_EVENT_LOG VALUES
(
ora_login_user ,
sysdate ,
ora_sysevent
);
END;
/
CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER_LOGOFF BEFORE LOGOFF ON DATABASE
BEGIN
INSERT
INTO USER_EVENT_LOG VALUES
(
ora_login_user ,
sysdate ,
ora_sysevent
);
END;
/
What is PRAGMA AUTONOMOUS TRANSACTION in PLSQL?
/*****************************************************************************
PRAGMA AUTONOMOUS_TRANSACTION
*****************************************************************************/
The Child Code runs independently of its parent
the Child code can commit or rollback and parent resumes
the parent code can continue without affecting child work
drop table my_test;
CREATE TABLE my_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO my_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO my_test (id, description) VALUES (2, 'Description for 2');
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO my_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
commit;
END;
/
select * from my_test;
rollback;
delete from my_test;
CREATE or REPLACE TRIGGER EMP_TRIGGER
BEFORE
INSERT ON emp
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
:new.ename := upper(:new.ename);
commit;
END;
/
insert into emp select * from scott.emp;
commit;
What is Mutating Table Error in Triggers
/*****************************************************************************
WHAT IS MUTATING TABLE ERROR???????????????????????????????????????????
*****************************************************************************/
CREATE TABLE mutate_tables1 ( num NUMBER );
CREATE TABLE mutate_tables2 ( id NUMBER );
INSERT INTO mutate_tables2 VALUES (NULL);
COMMIT;
CREATE OR REPLACE TRIGGER mutate_Trigger1
AFTER INSERT ON mutate_tables2
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE mutate_tables1
SET num = ( SELECT MAX(id) FROM mutate_tables2 );
COMMIT;
END;
/
INSERT INTO mutate_tables2 VALUES (1);
commit;
select * from mutate_tables2;
select * from mutate_tables1;
BULK COLLECT and BULK BIND in PLSQL
/*****************************************************************************
BULK COLLECT & FORALL (BULK BINDING)......
******************************************************************************/
SET SERVEROUTPUT ON;
create table bulk_table(no number);
create table bulk_bind(no number);
----------------------------
BEGIN
FOR i IN 1..1000000
LOOP
INSERT INTO bulk_table VALUES (i);
END LOOP;
COMMIT;
END;
/
select count(*) from bulk_table;
------------------------
DECLARE
TYPE rt IS TABLE OF bulk_table%rowtype;
vt rt;
BEGIN
SELECT * BULK COLLECT INTO vt FROM bulk_table;
FORALL i IN 1..vt.count
INSERT INTO bulk_bind VALUES vt(i);
COMMIT;
END;
/
----------------------------------
select count(*) from bulk_bind;