CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Data Integrity
Version 11.1
 
General Information
Data Dictionary Objects Related To Database Links
dba_constraints all_constraints user_constraints
dba_indexes all_indexes user_indexes
dba_tab_cols all_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;
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;
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;
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;
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;
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;
 
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
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
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [223 users online]    © 2010 psoug.org