| 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) |
----------------------------------------------------------- |
|
