JAVA J2EE 4 U

 heuristic analysis

Common Suffix Patterns (LIKE '%XYZ')

Pattern	Meaning / Use Case
_BAK	Backup copy of the table
_BACKUP	Same as above
_TMP	Temporary version of the table
_TEMP	Temporary or interim data
_COPY	Copy of an original table
_OLD	Old version, before a structural/data change
_NEW	New version during migration/ETL
_TEST	Used for testing
_ARCHIVE	Archived data
_STAGE	Staging table before transformation
_HIST	Historical data
_HISTORY	Extended historical storage
_SNAP	Snapshot of a live table
_MIG	Migration purposes
_EXPORT	Table used for export/dump
_IMPORT	Table used during import
_VALIDATION	For data validation during ETL
_DUP	Duplicate copy
_ROLLBACK	Used to rollback or undo a change

---

🛠️ Common Prefix Patterns (LIKE 'XYZ%')

Pattern	Meaning / Use Case
TMP_	Temporary table
STG_	Staging table
BK_	Backup copy
OLD_	Old version
NEW_	New version
ARCH_	Archived data
COPY_	Duplicate
TEST_	Testing tables
SNAP_	Snapshots
VAL_	Validation tables
DUP_	Duplicate
ROLLBACK_	Used for undo / rollback testing
T_	Often used as temp or test table

WITH table_defs AS (

  SELECT table_name,

         LISTAGG(column_name || ':' || data_type || ':' || data_length, ',') 

           WITHIN GROUP (ORDER BY column_id) AS col_signature

  FROM all_tab_columns

  WHERE owner = 'YOUR_SCHEMA'

  GROUP BY table_name

)

SELECT col_signature, COUNT(*), LISTAGG(table_name, ', ') WITHIN GROUP (ORDER BY table_name) AS tables

FROM table_defs

GROUP BY col_signature

HAVING COUNT(*) > 1;

SELECT 

    owner,

    table_name,

    num_rows,

    blocks,

    avg_row_len,

    ROUND(blocks * 8 / 1024, 2) AS size_mb, -- 1 block = 8KB by default

    last_analyzed

FROM dba_tables

WHERE owner = 'YOUR_SCHEMA'

  AND REGEXP_LIKE(table_name, 

       '(_BAK$|_BACKUP$|_TMP$|_TEMP$|_COPY$|_OLD$|_NEW$|_TEST$|_ARCH$|_ARCHIVE$|_STAGE$|_SNAP$|_DUP$|_EXPORT$|_IMPORT$|_HIST$|_HISTORY$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_|^ARCH_|^SNAP_|^COPY_|^VAL_|^DUP_)',

       'i') -- case-insensitive

  AND num_rows IS NOT NULL -- ensure stats exist

ORDER BY size_mb DESC;

SELECT 

    t.owner,

    t.table_name,

    t.tablespace_name,

    t.num_rows,

    t.blocks,

    ROUND(t.blocks * 8 / 1024, 2) AS size_mb,

    t.last_analyzed,

    MAX(s.end_time) AS last_access_time

FROM dba_tables t

JOIN dba_hist_seg_stat_obj o

    ON t.owner = o.owner AND t.table_name = o.object_name

JOIN dba_hist_seg_stat s

    ON o.obj# = s.obj#

WHERE t.owner = 'YOUR_SCHEMA'

  AND REGEXP_LIKE(t.table_name, 

       '(_BAK$|_BACKUP$|_TMP$|_TEMP$|_COPY$|_OLD$|_NEW$|_TEST$|_ARCH$|_ARCHIVE$|_STAGE$|_SNAP$|_DUP$|_EXPORT$|_IMPORT$|_HIST$|_HISTORY$|^TMP_|^STG_|^BK_|^OLD_|^NEW_|^TEST_|^ARCH_|^SNAP_|^COPY_|^VAL_|^DUP_)',

       'i')

GROUP BY 

    t.owner, t.table_name, t.tablespace_name, t.num_rows, t.blocks, t.last_analyzed

ORDER BY size_mb DESC;