Does Oracle table drop also trigger dropping all its sequences?

user_3764965 0
Viewed 54

I have an oracle table called for example APP_TABLE having a sequence SEQ_TABLE. I want to drop a table with its sequence in a single command.

Note the sequence has been generated through Hibernate (is it a "system-generated" sequence?)

<generator class="native">
    <param name="sequence">SEQ_TABLE</param>
</generator>

Does DROP TABLE APP_TABLE also trigger dropping its sequence in Oracle? I have searched in the documentation (I am using the 19c version) and I have learned all its indices and triggers are removed, but nothing about sequences (the information can be incomplete, though).

How to assure the underlying sequence is also removed without dropping it by explicitly finding out and performing a drop by its name?

oracle19c sequence oracle hibernate spring
2 Answers

If the sequence is genuinely 'part' of the table definition (in which case it is visibly linked in USER_TAB_IDENTITY_COLS) then it will be dropped. Otherwise it is an independent object and will remain.

user_257140

You can make drop sql for the sequences you want to remove if they are used in the trigger.

select 'DROP SEQUENCE '||REFERENCED_OWNER||'.'||REFERENCED_NAME||' ;'  from user_dependencies
where name in (select TRIGGER_NAME from user_triggers
                where BASE_OBJECT_TYPE = 'TABLE'
                  and TABLE_NAME = 'NAME_OF_TABLE')  
and referenced_type = 'SEQUENCE';
user_77347320
Related