oracle 删除外键的表的相关sql语句

 1. 查找所有引用某表主键为外键的表

其中PK_SRVMONITOR_SERVICE为该表的主键名

 

select c.constraint_name,
       c.table_name,
       c.constraint_type,
       c.r_constraint_name
  from user_constraints c
 where c.r_constraint_name = 'PK_SRVMONITOR_SERVICE'
   and c.constraint_type = 'R'

 

2.删除SM_BIZSYS_RES表中的的FK_BIZSYS_RES_RES_ID外键

ALTER TABLE SM_BIZSYS_RES DROP CONSTRAINT FK_BIZSYS_RES_RES_ID

 

 

3.增加 CONSTRAINT FK_SM_LASTPERF表中的SRVMONITOR_SERVICE表中的主键作为外键

ALTER TABLE  ADD CONSTRAINT FK_SM_LASTPERF_SRV_ID FOREIGN KEY(SRV_ID) REFERENCES SRVMONITOR_SERVICE(SRV_ID);

 

 4.查找当前用户下所有的sequence

select 'create sequence ' || sequence_name  || ' minvalue ' || min_value ||
       ' maxvalue ' || max_value || ' start with ' || last_number ||
       ' increment by ' || increment_by || (case
         when cache_size = 0 then
          ' nocache'
         else
          ' cache ' || cache_size
       end) || ';' seq
  from user_sequences