SQLPLus Query Tips

To copy table across databases/schemes.

Oracle SQLplus Copy Command:
SQL> create table table_name as select * from table_name;
SQL> copy from usr/pwd@SID to usr/pwd@SID create table table_name as select * from table_name;

Drop Constraint Example:– ALTER TABLE Employee DROP CONSTRAINT constraint_name;
– ALTER TABLE Employee disable/enable CONSTRAINT constraint_name;

ADD Constraint Examples
– ALTER TABLE Employee MODIFY (Department CONSTRAINT constraint_name_nn NOT NULL);
– ALTER TABLE Employee ADD/MODIFY
( CONSTRAINT constraint_name_fk FOREIGN KEY (DepartmentID) REFERENCES Department (DepartmentID) )
– ALTER TABLE Employee ADD CONSTRAINT Employee_pk PRIMARY KEY (EmployeeID)
– ALTER TABLE Employee ADD CONSTRAINT Employee_ck UNIQUE (EmployeeID, Status)

Full Table Scan

1- SELECT COUNT(idA)
FROM tabA
WHERE idA NOT IN (SELECT idA FROM tabB)

2- SELECT COUNT(a.idA)
FROM tabB b, tabA a
WHERE a.idA = b.idA (+) AND b.idA IS NULL

3- SELECT COUNT(a.idA)
FROM tabA a
WHERE NOT EXISTS (SELECT ‘X’ FROM tabB a WHERE a.idA = b.idA)

HAVING Clause

The HAVING clause filters selected rows only after all rows have been fetched.
SELECT d.dname, AVG (e.sal) FROM emp e, dept d WHERE e.deptno = d.deptno
GROUP BY d.dname HAVING dname != ‘RESEAECH’ AND dname != ‘SALES’;

Anti Joins

An anti-join is used to return rows from a table that that are present in another table.
SELECT * FROM dept WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);

Full Outer Joins

SELECT empno, ename, NVL(dept.deptno,emp.deptno) deptno, dname
FROM emp FULL OUTER JOIN dept ON (emp.deptno = dept.deptno) ORDER BY 1,2,3,4;

How can I Drop system generated constraint (NOT NULL CHECK)?

First:
here the query to get the constraint name for just a NOT NULL constraint

select * from user_constraints
where table_name = upper(‘cct_users’)
and constraint_type = ‘C’

Then:

CREATE OR REPLACE FUNCTION CC (P_CONSTRAINT VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
FOR R1 IN (SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME = P_CONSTRAINT) LOOP
DBMS_OUTPUT.PUT_LINE(R1.SEARCH_CONDITION);
RETURN R1.SEARCH_CONDITION;
EXIT;
END LOOP;
RETURN NULL;
END;
/

SET SERVEROUTPUT ON;

DECLARE
constname user_constraints.constraint_name%TYPE;
BEGIN
SELECT CONSTRAINT_NAME INTO constname FROM USER_CONSTRAINTS
WHERE CC(CONSTRAINT_NAME) LIKE ‘%your field NOT NULL CHECK%‘
AND lower(table_name) = ‘your_table_name ‘;
EXECUTE IMMEDIATE (‘ALTER TABLE your_table_name DROP CONSTRAINT ‘ || constname);
dbms_output.put_line(‘Constraint dropped succesfully!”);
END;