The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1
and version 18.0 is going to be available soon.
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 and beyond.
Have you viewed the Library's main Table of Contents? If not ... [Click Here]
... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
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);
