Oracle Data Integrity
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Data Dictionary Objects
ALL_CONSTRAINTS CDB_INDEXES DBA_TAB_COLS
ALL_INDEXES CDB_TAB_COLS USER_CONSTRAINTS
ALL_TAB_COLS DBA_CONSTRAINTS USER_INDEXES
CDB_CONSTRAINTS DBA_INDEXES 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-2014 Daniel A. Morgan All Rights Reserved