Document Type | Technical Information
Category | Migration
Applicable Product Versions | 6FS07PS, 7FS02PS
Document Number | TMITI006
Overview
After migrating from Oracle to Tibero or upgrading from Tibero 6 to Tibero 7, it is often necessary to verify that the row count of the target tables matches exactly before and after the migration.
This document guides you on how to collect and compare row counts for each table using GLOBAL TEMPORARY TABLE (GTT) as a method of row count verification.
NoteWhen performing count queries using UNION ALL, to avoid excessive memory usage, it is recommended to perform them individually using PL/SQL syntax.
Method
1. Create GLOBAL TEMP TABLE
CREATE GLOBAL TEMPORARY TABLE MIG_TAB_COUNT ( owner VARCHAR(40), table_name VARCHAR(128), row_count NUMBER ) ON COMMIT PRESERVE ROWS; -- Preserved per session. Automatically cleared when session ends (On commit preserve rows)
2. Collect Row Counts (for local tables)
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR i IN (SELECT owner, table_name FROM DBA_TABLES
WHERE 1=1
-- AND owner IN ('TIBERO1','TIBERO2')
) LOOP
v_sql := 'INSERT INTO MIG_TAB_COUNT (owner, table_name, row_count) ' ||
'SELECT ''' || i.owner || ''', ''' || i.table_name || ''', COUNT(*) FROM ' || i.owner || '.' || i.table_name;
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
3. Check Results
Copy the results to Excel and compare with the data before migration. (Perform separately on As-is and To-be)
SELECT * FROM MIG_TAB_COUNT;
4. Collect Row Counts Using DBLINK (Optional)
DECLARE
v_sql VARCHAR2(1000);
BEGIN
FOR i IN (SELECT owner, table_name FROM ALL_TABLES
WHERE 1=1
-- AND owner IN ('TIBERO1','TIBERO2')
) LOOP
v_sql := 'INSERT INTO MIG_TAB_COUNT (owner, table_name, row_count) ' ||
'SELECT ''' || i.owner || ''', ''' || i.table_name || ''', COUNT(*) FROM ' || i.owner || '.' || i.table_name || '@olink';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;Copy the results to Excel and compare with the data before migration. (Perform separately on As-is and To-be)
DROP TABLE MIG_TAB_COUNT PURGE;
Note