How to Clean Up ODI SNP_ Tables from an Oracle Database

In Oracle Data Integrator (ODI) environments, there may come a time when you need to clean up the repository tables, especially when decommissioning an environment, resetting a schema, or preparing for a fresh installation.

ODI repository tables often start with the prefix SNP_. In this post, we'll walk through how to identify and generate the SQL to drop these tables and confirm they’ve been successfully removed.

Our goal is to drop all tables starting with SNP_ from a schema that previously held an ODI repository.

Step 1: Generate DROP Statements for All SNP_ Tables

SELECT 'DROP TABLE ' || table_name || ' ;' 

FROM dba_tables 

WHERE table_name LIKE 'SNP_%';

QL>  select ' drop table ' || table_name ||' ;' from dba_tables where table_name like  'SNP_%';

'DROPTABLE'||TABLE_NAME||';'
--------------------------------------------
 drop table SNP_ACTION ;
 drop table SNP_AGENT ;
 drop table SNP_AGENT_PROP ;
 drop table SNP_AGENT_PROP_LST ;
 drop table SNP_ALLOC_AGENT ;
 drop table SNP_COL ;
 drop table SNP_COND ;
 drop table SNP_CONNECT ;
 drop table SNP_CONNECT_PROP ;
 drop table SNP_CONTEXT ;
 drop table SNP_CONV_DT ;
 drop table SNP_DATA ;
 drop table SNP_DATASOURCE ;
 drop table SNP_DATA_SET ;
 drop table SNP_DIAGRAM ;
 drop table SNP_DIAG_PARAM ;
 drop table SNP_DT ;
 drop table SNP_ENTITY ;
 drop table SNP_ENT_ID ;
 drop table SNP_ESS ;
 drop table SNP_EXP_TXT ;
 drop table SNP_EXP_TXT_HEADER ;
 drop table SNP_FF_VALUEE ;
 drop table SNP_FF_VALUEM ;
 drop table SNP_FF_VALUEW ;
 drop table SNP_FIELD ;
 drop table SNP_FIELD_LOOKUP ;
 drop table SNP_FLEX_FIELD ;
 drop table SNP_FOLDER ;
 drop table SNP_GRP_ACTION ;
 drop table SNP_GRP_STATE ;
 drop table SNP_HIST_VERSION ;
 drop table SNP_HOST ;
 drop table SNP_HOST_MOD ;
 drop table SNP_ID ;
 drop table SNP_IMPORT_REP ;
 drop table SNP_INDEX_TYPE ;
 drop table SNP_INDIRECT_MREF ;
 drop table SNP_INST_OBJ ;
 drop table SNP_JOIN ;
 drop table SNP_JOIN_COL ;
 drop table SNP_KEY ;
 drop table SNP_KEY_COL ;
 drop table SNP_LAGENT ;
 drop table SNP_LANG ;
 drop table SNP_LANG_ELT ;
 drop table SNP_LANG_TECHNO ;
 drop table SNP_LB_AGENT ;
 drop table SNP_LE_TECHNO ;
 drop table SNP_LICENSE ;
 drop table SNP_LINE_ACTION ;
 drop table SNP_LINE_TRT ;
 drop table SNP_LINE_TRT_UE ;
 drop table SNP_LINK ;
 drop table SNP_LINK_COORD ;
 drop table SNP_LINK_DIAG ;
 drop table SNP_LOAD_PLAN ;
 drop table SNP_LOCK ;
 drop table SNP_LOCKED_OBJECT ;
 drop table SNP_LOC_REP ;
 drop table SNP_LOC_REPW ;
 drop table SNP_LOOKUP ;
 drop table SNP_LOOKUP_VALUE ;
 drop table SNP_LPI_EC_VAR_LOG ;
 drop table SNP_LPI_EXC_LOG ;
 drop table SNP_LPI_RUN ;
 drop table SNP_LPI_STEP ;
 drop table SNP_LPI_STEP_LOG ;
 drop table SNP_LPI_STEP_VAR ;
 drop table SNP_LPI_TXT_LOG ;
 drop table SNP_LPI_VAR ;
 drop table SNP_LPI_VAR_LOG ;
 drop table SNP_LP_INST ;
 drop table SNP_LP_STEP ;
 drop table SNP_LP_STEP_VAR ;
 drop table SNP_LP_VAR ;
 drop table SNP_LSCHEMA ;
 drop table SNP_METHOD ;
 drop table SNP_MISSING_REF ;
 drop table SNP_MODEL ;
 drop table SNP_MODULE ;
 drop table SNP_MOD_FOLDER ;
 drop table SNP_MORIG_TXT ;
 drop table SNP_MTXT ;
 drop table SNP_MTXT_PART ;
 drop table SNP_M_IMPORT_REP ;
 drop table SNP_OBJECT ;
 drop table SNP_OBJECT_ID ;
 drop table SNP_OBJECT_ID_COL ;
 drop table SNP_OBJ_DIAG ;
 drop table SNP_OBJ_STATE ;
 drop table SNP_OBJ_TRACE ;
 drop table SNP_OPEN_TOOL ;
 drop table SNP_ORIG_TXT ;
 drop table SNP_PACKAGE ;
 drop table SNP_PARAM_LPI_RUN ;
 drop table SNP_PARAM_SESS ;
 drop table SNP_PARTITION ;
 drop table SNP_PLAN_AGENT ;
 drop table SNP_POP ;
 drop table SNP_POP_CLAUSE ;
 drop table SNP_POP_COL ;
 drop table SNP_POP_CONSTRAINT ;
 drop table SNP_POP_CONTEXT ;
 drop table SNP_POP_MAPPING ;
 drop table SNP_PROFILE ;
 drop table SNP_PROF_METH ;
 drop table SNP_PROJECT ;
 drop table SNP_PSCHEMA ;
 drop table SNP_PSCHEMA_CONT ;
 drop table SNP_PWD_POLICY ;
 drop table SNP_PWD_RULE ;
 drop table SNP_REM_REP ;
 drop table SNP_REV_COL ;
 drop table SNP_REV_COL_FFV ;
 drop table SNP_REV_COND ;
 drop table SNP_REV_COND_FFV ;
 drop table SNP_REV_FOR_TABLE ;
 drop table SNP_REV_JOIN ;
 drop table SNP_REV_JOIN_COL ;
 drop table SNP_REV_JOIN_FFV ;
 drop table SNP_REV_KEY ;
 drop table SNP_REV_KEY_COL ;
 drop table SNP_REV_KEY_FFV ;
 drop table SNP_REV_MODEL_FFV ;
 drop table SNP_REV_PARTITION ;
 drop table SNP_REV_TABLE ;
 drop table SNP_REV_TABLE_FFV ;
 drop table SNP_SCEN ;
 drop table SNP_SCEN_FOLDER ;
 drop table SNP_SCEN_REPORT ;
 drop table SNP_SCEN_STEP ;
 drop table SNP_SCEN_TASK ;
 drop table SNP_SCEN_TXT ;
 drop table SNP_SEQUENCE ;
 drop table SNP_SEQ_DATA ;
 drop table SNP_SEQ_SCEN ;
 drop table SNP_SEQ_SESS ;
 drop table SNP_SESSION ;
 drop table SNP_SESS_FOLDER ;
 drop table SNP_SESS_STEP ;
 drop table SNP_SESS_STEP_LV ;
 drop table SNP_SESS_TASK ;
 drop table SNP_SESS_TASK_LOG ;
 drop table SNP_SESS_TASK_LS ;
 drop table SNP_SESS_TXT_LOG ;
 drop table SNP_SOLUTION ;
 drop table SNP_SOL_ELT ;
 drop table SNP_SOURCE_TAB ;
 drop table SNP_SRC_SET ;
 drop table SNP_STATE ;
 drop table SNP_STATE2 ;
 drop table SNP_STEP ;
 drop table SNP_STEP_LOG ;
 drop table SNP_STEP_REPORT ;
 drop table SNP_SUB_LANG ;
 drop table SNP_SUB_MODEL ;
 drop table SNP_TABLE ;
 drop table SNP_TASK_TXT ;
 drop table SNP_TECHNO ;
 drop table SNP_TRT ;
 drop table SNP_TXT ;
 drop table SNP_TXT_CROSSR ;
 drop table SNP_TXT_HEADER ;
 drop table SNP_UE_ORIG ;
 drop table SNP_UE_USED ;
 drop table SNP_UFUNC ;
 drop table SNP_UFUNC_IMPL ;
 drop table SNP_UFUNC_TECHNO ;
 drop table SNP_USER ;
 drop table SNP_USER_EXIT ;
 drop table SNP_USER_METH ;
 drop table SNP_USER_OBJ_METH ;
 drop table SNP_USER_PREF ;
 drop table SNP_USER_PROF ;
 drop table SNP_U_O_M_REP ;
 drop table SNP_VAR ;
 drop table SNP_VAR_DATA ;
 drop table SNP_VAR_PLAN_AGENT ;
 drop table SNP_VAR_SCEN ;
 drop table SNP_VAR_SESS ;
 drop table SNP_VERSION ;

182 rows selected.

SQL>

Step 2: Run the DROP Statements

spool the output to a script and run it, or execute the SQLs manually depending on your need. This effectively removes all ODI metadata tables from the schema.

Important: Ensure  you no longer need the data in these tables. This action is irreversible.

SQL>@spool_SNP.sql

Step 3: Verify Table Removal

SQL> select table_name from all_tables where table_name like 'SNP_%';

no rows selected

SQL>

Conclusion

Cleaning up an Oracle schema by dropping ODI-specific tables is a straightforward process when using dynamic SQL. It’s a useful approach for DBAs and ODI administrators performing:

• Environment resets
• Repository migrations
• Clean installations

Always ensure a backup exists before performing destructive operations, and double-check you're working in the correct schema.