Oracle Data Integrity
Version 11.2.0.3

General Information
Data Dictionary Objects
ALL_CONSTRAINTS DBA_CONSTRAINTS USER_CONSTRAINTS
ALL_INDEXES DBA_INDEXES USER_INDEXES
ALL_TAB_COLS DBA_TAB_COLS USER_TAB_COLS
 
Record Level Integrity
Primary Keys SELECT COUNT(*)
FROM user_tables;

SELECT COUNT(*)
FROM user_constraints
WHERE constraint_type = 'P';
 
Column Definition Integrity
CHAR conn oe/oe

set linesize 131
set pagesize 25
col COLNAME format a30
col t1dt format a10
col t2dt format a10

SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'CHAR'
AND t2.data_type <> 'CHAR'
ORDER BY t1.column_name, t1.table_name;
DATE SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'DATE'
AND t2.data_type <> 'DATE'
ORDER BY t1.column_name, t1.table_name;
FLOAT SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'FLOAT'
AND t2.data_type <> 'FLOAT'
ORDER BY t1.column_name, t1.table_name;
NUMBER SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'NUMBER'
AND t2.data_type <> 'NUMBER'
ORDER BY t1.column_name, t1.table_name;
TIMESTAMP SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'TIMESTAMP'
AND t2.data_type <> 'TIMESTAMP'
ORDER BY t1.column_name, t1.table_name;
VARCHAR2 SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type T1DT, t2.table_name T2T, t2.data_type T2DT
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = 'VARCHAR2'
AND t2.data_type <> 'VARCHAR2'
ORDER BY t1.column_name, t1.table_name;
 
Data Length Integrity
NUMBER conn oe/oe

SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t2.table_name T2T, t2.data_length T2DL
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'NUMBER'
AND t1.data_length <> t2.data_length
ORDER BY t1.column_name, t1.table_name;
VARCHAR2 conn uwclass/uwclass

CREATE TABLE t1 AS SELECT table_name FROM user_tables;

CREATE TABLE t2 AS SELECT table_name FROM user_tables;

ALTER TABLE t2 MODIFY (table_name VARCHAR2(43));

set linesize 131
col t1t format a25
col t2t format a25
col colname format a20
col data_type format a15

SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t2.table_name T2T, t2.data_length T2DL
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'VARCHAR2'
AND t1.data_length <> t2.data_length
ORDER BY t1.column_name, t1.table_name;
 
Data Precision Integrity
NUMBER CREATE TABLE t1 AS
SELECT initial_extent
FROM all_tables;

CREATE TABLE t2 AS
SELECT * FROM t1
WHERE 1=2;

ALTER TABLE t2 MODIFY (initial_extent NUMBER(10,4));

set linesize 141
col data_type format a15
col T1T format a15
col T2T format a15

SELECT t1.table_name T1T, t1.column_name COLNAME, t1.data_type,
t1.data_length T1DL, t1.data_precision T1DP, t2.table_name T2T,
t2.data_length T2DL, t2.data_precision T2DP
FROM user_tab_cols t1, user_tab_cols t2
WHERE t1.table_name <> t2.table_name
AND t1.column_name = t2.column_name
AND t1.data_type = t2.data_type
AND t1.data_type = 'NUMBER'
AND NVL(t1.data_precision,0) <> NVL(t2.data_precision,0)
ORDER BY t1.column_name, t1.table_name;
 
Removing Duplicates
Using an analytic function conn uwclass/uwclass

CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

INSERT INTO t
SELECT * FROM t;

COMMIT;

SELECT * FROM t ORDER BY 1,2;

EXPLAIN PLAN FOR
DELETE t
WHERE ROWID IN (
  SELECT LEAD(ROWID) OVER (PARTITION BY table_name, tablespace_name ORDER BY NULL)
FROM t);

SELECT * FROM TABLE(dbms_xplan.display);

DELETE t
WHERE ROWID IN (
  SELECT LEAD(ROWID)
  OVER (PARTITION BY table_name, tablespace_name ORDER BY NULL)
FROM t);

SELECT * FROM t ORDER BY 1,2;

-------------------------------------------------------------
| Id | Operation               | Rows | Bytes | Cost (%CPU) |
-------------------------------------------------------------
|  0 | DELETE STATEMENT        |  433 | 10392 |   7    (43) |
|  1 |  DELETE                 |      |       |             |
|  2 |   HASH JOIN             |  433 | 10392 |   7    (43) |
|  3 |    VIEW                 |  208 |  2496 |   4    (50) |
|  4 |     SORT UNIQUE         |  208 |  9568 |   4    (50) |
|  5 |      WINDOW SORT        |  208 |  9568 |   4    (50) |
|  6 |       TABLE ACCESS FULL |  208 |  9568 |   2     (0) |
|  7 |    TABLE ACCESS FULL    |  208 |  2496 |   2     (0) |
-------------------------------------------------------------
2 - access(ROWID="$nso_col_1")
Using GROUP BY CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

INSERT INTO t
SELECT * FROM t;

COMMIT;

SELECT * FROM t ORDER BY 1,2;

EXPLAIN PLAN FOR
DELETE FROM t
WHERE rowid NOT IN (
  SELECT MIN(ROWID)
  FROM t
  GROUP BY table_name, tablespace_name);

SELECT * FROM TABLE(dbms_xplan.display);

DELETE FROM t
WHERE rowid NOT IN (
  SELECT MIN(rowid)
  FROM t
  GROUP BY table_name, tablespace_name);

SELECT * FROM t ORDER BY 1, 2;

-----------------------------------------------------------
| Id | Operation              | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------
|  0 | DELETE STATEMENT       |    1 |    24 |  22   (34) |
|  1 |  DELETE                |      |       |            |
|  2 |   HASH JOIN ANTI       |    1 |    24 |   6   (34) |
|  3 |    TABLE ACCESS FULL   |  208 |  2496 |   2    (0) |
|  4 |    VIEW                |  208 |  2496 |   3   (34) |
|  5 |     SORT GROUP BY      |  208 |  9568 |   3   (34) |
|  6 |      TABLE ACCESS FULL |  208 |  9568 |   2    (0) |
-----------------------------------------------------------

Related Topics
Constraints
Select Statements

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved