| General Information |
| Library Note |
Morgan's Library Page Header
|
| 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@pdbdev
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@pdbdev
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@pdbdev
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@pdbdev
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) |
----------------------------------------------------------- |