오라클에서 하나의 레코드를 가리키는 외부 키 종속성을 찾는 방법은 무엇입니까?
많은 테이블과 수백만 개의 행이 있는 매우 큰 Oracle 데이터베이스를 보유하고 있습니다.이 중 하나를 삭제해야 하지만 삭제하지 않으면 외부 키 레코드로 표시되는 종속 행이 손상되지 않습니다.이 행을 가리키는 다른 모든 레코드 또는 적어도 테이블 스키마의 목록을 가져올 수 있는 방법이 있습니까?내가 직접 삭제하고 예외를 포착할 수 있다는 것을 알고 있지만, 스크립트를 직접 실행하지는 않을 것이며 처음부터 제대로 실행할 수 있도록 스크립트가 필요합니다.
Oracle의 SQL Developer와 AllRound Automation의 PL/SQL Developer 도구를 마음대로 사용할 수 있습니다.
잘 부탁드립니다!
표에 대한 모든 참조를 나열하는 솔루션은 다음과 같습니다.
select
src_cc.owner as src_owner,
src_cc.table_name as src_table,
src_cc.column_name as src_column,
dest_cc.owner as dest_owner,
dest_cc.table_name as dest_table,
dest_cc.column_name as dest_column,
c.constraint_name
from
all_constraints c
inner join all_cons_columns dest_cc on
c.r_constraint_name = dest_cc.constraint_name
and c.r_owner = dest_cc.owner
inner join all_cons_columns src_cc on
c.constraint_name = src_cc.constraint_name
and c.owner = src_cc.owner
where
c.constraint_type = 'R'
and dest_cc.owner = 'MY_TARGET_SCHEMA'
and dest_cc.table_name = 'MY_TARGET_TABLE'
--and dest_cc.column_name = 'MY_OPTIONNAL_TARGET_COLUMN'
;
이 솔루션을 사용하면 대상 테이블의 어떤 열이 대상 테이블의 어떤 열을 참조하는지(필터링할 수 있음) 정보도 얻을 수 있습니다.
저는 항상 출발 테이블의 외국인 키를 보고 돌아오는 길에 일합니다.DB 도구에는 일반적으로 종속성 또는 제약 조건 노드가 있습니다.PL/SQL Developer가 FK를 볼 수 있는 방법이 있다는 것은 알지만, 사용한 지가 오래되어서 설명할 수가 없습니다...
XXXXXXXXXXXXXXX를 테이블 이름으로 바꾸기만 하면 됩니다...
/* The following query lists all relationships */
select
a.owner||'.'||a.table_name "Referenced Table"
,b.owner||'.'||b.table_name "Referenced by"
,b.constraint_name "Foreign Key"
from all_constraints a, all_constraints b
where
b.constraint_type = 'R'
and a.constraint_name = b.r_constraint_name
and b.table_name='XXXXXXXXXXXX' -- Table name
order by a.owner||'.'||a.table_name
저는 최근에 비슷한 문제를 겪었지만, 곧 직접적인 의존성을 찾는 것만으로는 충분하지 않다는 것을 경험했습니다.그래서 저는 다단계 외부 키 종속성의 트리를 보여주기 위해 쿼리를 작성했습니다.
SELECT LPAD(' ',4*(LEVEL-1)) || table1 || ' <-- ' || table2 tables, table2_fkey
FROM
(SELECT a.table_name table1, b.table_name table2, b.constraint_name table2_fkey
FROM user_constraints a, user_constraints b
WHERE a.constraint_type IN('P', 'U')
AND b.constraint_type = 'R'
AND a.constraint_name = b.r_constraint_name
AND a.table_name != b.table_name
AND b.table_name <> 'MYTABLE')
CONNECT BY PRIOR table2 = table1 AND LEVEL <= 5
START WITH table1 = 'MYTABLE';
데이터베이스에서 발송을 내 테이블로 사용하면 다음과 같은 결과가 나타납니다.
SHIPMENT <-- ADDRESS
SHIPMENT <-- PACKING_LIST
PACKING_LIST <-- PACKING_LIST_DETAILS
PACKING_LIST <-- PACKING_UNIT
PACKING_UNIT <-- PACKING_LIST_ITEM
PACKING_LIST <-- PO_PACKING_LIST
...
데이터 사전을 사용하여 해당 테이블의 기본 키를 참조하는 테이블을 식별할 수 있습니다.이를 통해 동적 SQL을 생성하여 해당 테이블에 zap할 값을 쿼리할 수 있습니다.
SQL> declare
2 n pls_integer;
3 tot pls_integer := 0;
4 begin
5 for lrec in ( select table_name from user_constraints
6 where r_constraint_name = 'T23_PK' )
7 loop
8 execute immediate 'select count(*) from '||lrec.table_name
9 ||' where col2 = :1' into n using &&target_val;
10 if n = 0 then
11 dbms_output.put_line('No impact on '||lrec.table_name);
12 else
13 dbms_output.put_line('Uh oh! '||lrec.table_name||' has '||n||' hits!');
14 end if;
15 tot := tot + n;
16 end loop;
17 if tot = 0
18 then
19 delete from t23 where col2 = &&target_val;
20 dbms_output.put_line('row deleted!');
21 else
22 dbms_output.put_line('delete aborted!');
23 end if;
24 end;
25 /
Enter value for target_val: 6
No impact on T34
Uh oh! T42 has 2 hits!
No impact on T69
delete aborted!
PL/SQL procedure successfully completed.
SQL>
이 예는 약간 부정행위를 합니다.대상 기본 키의 이름은 하드 코딩되고 참조 열은 모든 종속 테이블에서 동일한 이름을 가집니다.이러한 문제를 해결하는 것은 독자에게 연습으로 남겨집니다 ;)
비슷한 상황이 있었습니다.저의 경우, 동일한 ID를 가진 두 개의 기록이 경우에 따라 다릅니다.각 레코드에 대해 어떤 종속 레코드가 존재하는지 확인하고 어떤 레코드를 삭제/업데이트하기가 가장 쉬웠는지 확인하려고 함
다음은 각 테이블/마스터 레코드 조합에 대한 카운트와 함께 하위 테이블별로 지정된 레코드를 가리키는 모든 하위 레코드를 출력합니다.
declare
--
-- Finds and prints out how many children there are per table and value for each value of a given field
--
-- Name of the table to base the query on
cTable constant varchar2(20) := 'FOO';
-- Name of the column to base the query on
cCol constant varchar2(10) := 'ID';
-- Cursor to find interesting values (e.g. duplicates) in master table
cursor cVals is
select id
from foo f
where exists ( select 1 from foo f2
where upper(f.id) = upper(f2.id)
and f.rowid != f2.rowid );
-- Everything below here should just work
vNum number(18,0);
vSql varchar2(4000);
cOutColSize number(2,0) := 30;
cursor cReferencingTables is
select
consChild.table_name,
consChild.constraint_name,
colChild.column_name
from user_constraints consMast
inner join user_constraints consChild on consMast.constraint_name = consChild.r_constraint_name
inner join USER_CONS_COLUMNS colChild on consChild.CONSTRAINT_NAME = colChild.CONSTRAINT_NAME
inner join USER_CONS_COLUMNS colMast on colMast.CONSTRAINT_NAME = consMast.CONSTRAINT_NAME
where consChild.constraint_type = 'R'
and consMast.table_name = cTable
and colMast.column_name = cCol
order by consMast.table_name, consChild.table_name;
begin
dbms_output.put_line(
rpad('Table', cOutColSize) ||
rpad('Column', cOutColSize) ||
rpad('Value', cOutColSize) ||
rpad('Number', cOutColSize)
);
for rRef in cReferencingTables loop
for rVals in cVals loop
vSql := 'select count(1) from ' || rRef.table_name || ' where ' || rRef.column_name || ' = ''' || rVals.id || '''';
execute immediate vSql into vNum;
if vNum > 0 then
dbms_output.put_line(
rpad(rRef.table_name, cOutColSize) ||
rpad(rRef.column_name, cOutColSize) ||
rpad(rVals.id, cOutColSize) ||
rpad(vNum, cOutColSize) );
end if;
end loop;
end loop;
end;
select c.owner, a.table_name, a.column_name, a.constraint_name,
c.r_owner as ref_owner, cpk.table_name as ref_table,
cpk.constraint_name as ref_pk
from all_cons_columns a
join all_constraints c on a.owner = c.owner
and a.constraint_name = c.constraint_name
join all_constraints cpk on c.r_owner = cpk.owner
and c.r_constraint_name = cpk.constraint_name
where c.constraint_type = 'r' and c.table_name= 'table_name';
외부 키 관계를 기반으로 한 테이블의 종속 순서를 찾는 것이 얼마나 어려운지에 놀랐습니다.모든 테이블에서 데이터를 삭제하고 다시 가져오고 싶어서 필요했습니다.테이블을 종속성 순서로 나열하기 위해 작성한 쿼리입니다.아래 쿼리를 사용하여 삭제를 스크립팅하고 쿼리 결과를 역순으로 다시 가져올 수 있었습니다.
SELECT referenced_table
,MAX(lvl) for_deleting
,MIN(lvl) for_inserting
FROM
( -- Hierarchy of dependencies
SELECT LEVEL lvl
,t.table_name referenced_table
,b.table_name referenced_by
FROM user_constraints A
JOIN user_constraints b
ON A.constraint_name = b.r_constraint_name
and b.constraint_type = 'R'
RIGHT JOIN user_tables t
ON t.table_name = A.table_name
START WITH b.table_name IS NULL
CONNECT BY b.table_name = PRIOR t.table_name
)
GROUP BY referenced_table
ORDER BY for_deleting, for_inserting;
Oracle 제약 조건은 테이블 인덱스를 사용하여 데이터를 참조합니다.
어떤 테이블이 한 테이블을 참조하는지 알아보려면 역순으로 인덱스를 찾습니다.
/* Toggle ENABLED and DISABLE status for any referencing constraint: */
select 'ALTER TABLE '||b.owner||'.'||b.table_name||' '||
decode(b.status, 'ENABLED', 'DISABLE ', 'ENABLE ')||
'CONSTRAINT '||b.constraint_name||';'
from all_indexes a,
all_constraints b
where a.table_name='XXXXXXXXXXXX' -- Table name
and a.index_name = b.r_constraint_name;
Ob.: 참조를 비활성화하면 DML 명령(업데이트, 삭제 및 삽입)의 시간이 상당히 단축됩니다.
이는 모든 데이터가 일관성이 있다는 것을 알고 있는 대량 작업에 많은 도움이 될 수 있습니다.
/* List which columns are referenced in each constraint */
select ' TABLE "'||b.owner||'.'||b.table_name||'"'||
'('||listagg (c.column_name, ',') within group (order by c.column_name)||')'||
' FK "'||b.constraint_name||'" -> '||a.table_name||
' INDEX "'||a.index_name||'"'
"REFERENCES"
from all_indexes a,
all_constraints b,
all_cons_columns c
where rtrim(a.table_name) like 'XXXXXXXXXXXX' -- Table name
and a.index_name = b.r_constraint_name
and c.constraint_name = b.constraint_name
group by b.owner, b.table_name, b.constraint_name, a.table_name, a.index_name
order by 1;
언급URL : https://stackoverflow.com/questions/2509512/how-to-find-foreign-key-dependencies-pointing-to-one-record-in-oracle
'programing' 카테고리의 다른 글
| TypeScript 파일에서 Vue 구성 요소 가져오기 (0) | 2023.06.10 |
|---|---|
| iOS에서 HTML 양식 필드의 자동 대문자화를 해제하려면 어떻게 해야 합니까? (0) | 2023.06.10 |
| 가입 또는 로그인 없이 로컬에서 Azure 서비스 버스 테스트 (0) | 2023.06.10 |
| Android 앱이 백그라운드로 이동했다가 다시 백그라운드로 이동할 때 탐지하는 방법 (0) | 2023.06.10 |
| Vuex gettres가 맵 함수에서 데이터를 반환한다고 선언하는 방법 (0) | 2023.06.10 |