List Oracle sequences not in use

seq_not_in_use.sql

-- first, update dbname string

SELECT sequence_name FROM ALL_SEQUENCES WHERE SEQUENCE_OWNER = 
'dbname' and sequence_name like 'ISEQ$$_%' AND sequence_name not in (
    with xml     as ( select dbms_xmlgen.getxmltype( 'select 
column_name, data_default from all_tab_columns where owner=''dbname'' 
AND DATA_DEFAULT IS NOT NULL ') x from dual),
        col_defs as ( select extractValue(xt.object_value, 
'/ROW/COLUMN_NAME') as col_name,   extractValue(xt.object_value, 
'/ROW/DATA_DEFAULT') as d_def  from  xml,
            table(xmlsequence(extract(xml.x, '/ROWSET/ROW'))) xt
    )
    select REPLACE(REPLACE(to_char(d_def), '"dbname"."' , ''), 
'".nextval', '') from col_defs
);