oracle删除外键的表的相关sql语句
1. 查找所有引用某表主键为外键的表
其中PK_SRVMONITOR_SERVICE为该表的主键名
Sql代码
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外键
Sql代码
ALTER TABLE SM_BIZSYS_RES DROP CONSTRAINT FK_BIZSYS_RES_RES_ID
3.增加 CONSTRAINT FK_SM_LASTPERF表中的SRVMONITOR_SERVICE表中的主键作为外键
Sql代码
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
