반응형
특정 값 (오라클)에 대한 모든 테이블의 모든 필드 검색
모든 테이블의 모든 필드에서 Oracle의 특정 값을 검색 할 수 있습니까?
일부 테이블에는 수천 개의 행이있는 수백 개의 테이블이 있으므로 쿼리하는 데 시간이 오래 걸릴 수 있음을 알고 있습니다. 그러나 내가 아는 유일한 것은 내가 쿼리하려는 필드의 값이 1/22/2008P09RR8입니다. <
아래의 문장을 사용하여 이름을 지정해야한다고 생각하는 것을 기반으로 적절한 열을 찾으려고했지만 결과가 반환되지 않았습니다.
SELECT * from dba_objects
WHERE object_name like '%DTN%'
이 데이터베이스에는 문서가 전혀 없으며이 필드를 어디에서 가져 왔는지 전혀 알 수 없습니다.
이견있는 사람?
인용문:
아래에서이 문을 사용하여 명명해야한다고 생각하는 것을 기반으로 적절한 열을 찾으려고했지만 결과가 반환되지 않았습니다. *
SELECT * from dba_objects WHERE object_name like '%DTN%'
열은 개체가 아닙니다. 열 이름이 '% DTN %'와 같을 것으로 예상하는 경우 원하는 쿼리는 다음과 같습니다.
SELECT owner, table_name, column_name FROM all_tab_columns WHERE column_name LIKE '%DTN%';
그러나 'DTN'문자열이 당신의 추측 일뿐이라면 아마 도움이되지 않을 것입니다.
그런데 '1 / 22 / 2008P09RR8'이 단일 열에서 직접 선택된 값이라는 것이 얼마나 확실합니까? 어디에서 왔는지 전혀 모를 경우 여러 열을 연결하거나 함수의 결과 또는 중첩 테이블 개체에 저장된 값일 수 있습니다. 따라서 모든 값을 확인하기 위해 거위 추적이 진행될 수 있습니다. 이 값을 표시하는 클라이언트 응용 프로그램으로 시작하여 값을 얻기 위해 어떤 쿼리를 사용하고 있는지 파악할 수 없습니까?
어쨌든, diciu의 답변은 SQL 쿼리를 생성하여 모든 테이블의 모든 열에 값이 있는지 확인하는 한 가지 방법을 제공합니다. PL / SQL 블록과 동적 SQL을 사용하여 하나의 SQL 세션에서 완전히 유사한 작업을 수행 할 수도 있습니다. 이를 위해 성급하게 작성된 코드가 있습니다.
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
BEGIN
FOR t IN (SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE owner <> 'SYS' and data_type LIKE '%CHAR%') LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM ' || t.owner || '.' || t.table_name ||
' WHERE '||t.column_name||' = :1'
INTO match_count
USING '1/22/2008P09RR8';
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
더 효율적으로 만들 수있는 몇 가지 방법이 있습니다.
이 경우 찾고자하는 값이 주어지면 NUMBER 또는 DATE 유형의 열을 명확하게 제거하여 쿼리 수를 줄일 수 있습니다. 유형이 '% CHAR %'와 같은 열로 제한 할 수도 있습니다.
열당 하나의 쿼리 대신 다음과 같이 테이블 당 하나의 쿼리를 작성할 수 있습니다.
SELECT * FROM table1
WHERE column1 = 'value'
OR column2 = 'value'
OR column3 = 'value'
...
;
한 명의 소유자 만 검색하는 경우 더 빨리 작동하도록 위의 코드를 약간 수정했습니다. 검색 대상에 맞게 3 개의 변수 v_owner, v_data_type 및 v_search_string을 변경하면됩니다.
SET SERVEROUTPUT ON SIZE 100000
DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='ENTER_USERNAME_HERE';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='string to search here...';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE '||t.column_name||' = :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
그렇습니다. DBA가 당신을 미워하고 신발을 바닥에 박아 놓으면 많은 I / O가 발생하고 캐시가 제거 될 때 데이터베이스 성능이 실제로 저하 될 수 있습니다.
select column_name from all_tab_columns c, user_all_tables u where c.table_name = u.table_name;
시작합니다.
나는 v$sessionand를 사용하여 실행중인 쿼리로 시작할 것 v$sqlarea입니다. 이것은 오라클 버전에 따라 변경됩니다. 이것은 공간을 좁히고 모든 것을 치지 않습니다.
나는 이것이 오래된 주제라는 것을 안다. 그러나 질문을 SQL사용하는 대신 수행 할 수 있는지 묻는 의견이 있습니다 PL/SQL. 그래서 해결책을 게시하는 것으로 생각했습니다.
아래 데모는 전체 SCHEMA에서 모든 테이블의 모든 열에서 VALUE 을 검색하는 것입니다 .
- CHARACTER 유형 검색
스키마 에서 값 KING을 찾아 보자 SCOTT.
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
SQL>
- NUMERIC 유형 검색
스키마 에서 값 20을 찾아 보자 SCOTT.
SQL> variable val NUMBER
SQL> exec :val := 20
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
20 DEPT DEPTNO
20 EMP DEPTNO
20 EMP HIREDATE
20 SALGRADE HISAL
20 SALGRADE LOSAL
SQL>
하위 문자열 일치를 비교할 다른 수정 된 버전이 있습니다. 이것은 Oracle 11g에서 작동합니다.
DECLARE
match_count INTEGER;
-- Type the owner of the tables you are looking at
v_owner VARCHAR2(255) :='OWNER_NAME';
-- Type the data type you are look at (in CAPITAL)
-- VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking at
v_search_string VARCHAR2(4000) :='%lower-search-sub-string%';
BEGIN
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE lower('||t.column_name||') like :1'
INTO match_count
USING v_search_string;
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
/
나는 이런 식으로 할 것입니다 (필요한 모든 선택을 생성하십시오). 나중에 sqlplus에 피드 할 수 있습니다.
echo "select table_name from user_tables;" | sqlplus -S user/pwd | grep -v "^--" | grep -v "TABLE_NAME" | grep "^[A-Z]" | while read sw;
do echo "desc $sw" | sqlplus -S user/pwd | grep -v "\-\-\-\-\-\-" | awk -F' ' '{print $1}' | while read nw;
do echo "select * from $sw where $nw='val'";
done;
done;
결과는 다음과 같습니다.
select * from TBL1 where DESCRIPTION='val'
select * from TBL1 where ='val'
select * from TBL2 where Name='val'
select * from TBL2 where LNG_ID='val'
그리고 그것이하는 일은- 각 필드에서 (desc에서) 각 필드 table_name를 user_tables가져 와서 필드가 'val'인 테이블에서 select *를 만듭니다.
빠른 실행을 위해 각 테이블의 모든 열이 아닌 각 테이블마다 한 번씩 실행되도록 Flood의 스크립트를 수정했습니다. Oracle 11g 이상이 필요합니다.
set serveroutput on size 100000
declare
v_match_count integer;
v_counter integer;
-- The owner of the tables to search through (case-sensitive)
v_owner varchar2(255) := 'OWNER_NAME';
-- A string that is part of the data type(s) of the columns to search through (case-insensitive)
v_data_type varchar2(255) := 'CHAR';
-- The string to be searched for (case-insensitive)
v_search_string varchar2(4000) := 'FIND_ME';
-- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
v_sql clob := '';
begin
for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in
(select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' || upper(v_data_type) || '%')
order by table_name) loop
v_counter := 0;
v_sql := '';
for cur_columns in (select column_name from all_tab_columns where
owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
if v_counter > 0 then
v_sql := v_sql || ' or ';
end if;
v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
v_counter := v_counter + 1;
end loop;
v_sql := 'select count(*) from ' || cur_tables.table_name || ' where ' || v_sql;
execute immediate v_sql
into v_match_count;
if v_match_count > 0 then
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
end if;
end loop;
exception
when others then
dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/
테이블과 컬럼 이름을 알고 있지만 각 스키마에 대해 문자열이 나타나는 횟수를 찾으려면 다음을 수행하십시오.
Declare
owner VARCHAR2(1000);
tbl VARCHAR2(1000);
cnt number;
ct number;
str_sql varchar2(1000);
reason varchar2(1000);
x varchar2(1000):='%string_to_be_searched%';
cursor csr is select owner,table_name
from all_tables where table_name ='table_name';
type rec1 is record (
ct VARCHAR2(1000));
type rec is record (
owner VARCHAR2(1000):='',
table_name VARCHAR2(1000):='');
rec2 rec;
rec3 rec1;
begin
for rec2 in csr loop
--str_sql:= 'select count(*) from '||rec.owner||'.'||rec.table_name||' where CTV_REMARKS like '||chr(39)||x||chr(39);
--dbms_output.put_line(str_sql);
--execute immediate str_sql
execute immediate 'select count(*) from '||rec2.owner||'.'||rec2.table_name||' where column_name like '||chr(39)||x||chr(39)
into rec3;
if rec3.ct <> 0 then
dbms_output.put_line(rec2.owner||','||rec3.ct);
else null;
end if;
end loop;
end;
전체 데이터베이스를 검색하는 절차 :
CREATE or REPLACE PROCEDURE SEARCH_DB(SEARCH_STR IN VARCHAR2, TAB_COL_RECS OUT VARCHAR2) IS
match_count integer;
qry_str varchar2(1000);
CURSOR TAB_COL_CURSOR IS
SELECT TABLE_NAME,COLUMN_NAME,OWNER,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE DATA_TYPE in ('NUMBER','VARCHAR2') AND OWNER='SCOTT';
BEGIN
FOR TAB_COL_REC IN TAB_COL_CURSOR
LOOP
qry_str := 'SELECT COUNT(*) FROM '||TAB_COL_REC.OWNER||'.'||TAB_COL_REC.TABLE_NAME||
' WHERE '||TAB_COL_REC.COLUMN_NAME;
IF TAB_COL_REC.DATA_TYPE = 'NUMBER' THEN
qry_str := qry_str||'='||SEARCH_STR;
ELSE
qry_str := qry_str||' like '||SEARCH_STR;
END IF;
--dbms_output.put_line( qry_str );
EXECUTE IMMEDIATE qry_str INTO match_count;
IF match_count > 0 THEN
dbms_output.put_line( qry_str );
--dbms_output.put_line( TAB_COL_REC.TABLE_NAME ||' '||TAB_COL_REC.COLUMN_NAME ||' '||match_count);
TAB_COL_RECS := TAB_COL_RECS||'@@'||TAB_COL_REC.TABLE_NAME||'##'||TAB_COL_REC.COLUMN_NAME;
END IF;
END LOOP;
END SEARCH_DB;
실행 진술
DECLARE
SEARCH_STR VARCHAR2(200);
TAB_COL_RECS VARCHAR2(200);
BEGIN
SEARCH_STR := 10;
SEARCH_DB(
SEARCH_STR => SEARCH_STR,
TAB_COL_RECS => TAB_COL_RECS
);
DBMS_OUTPUT.PUT_LINE('TAB_COL_RECS = ' || TAB_COL_RECS);
END;
샘플 결과
Connecting to the database test.
SELECT COUNT(*) FROM SCOTT.EMP WHERE DEPTNO=10
SELECT COUNT(*) FROM SCOTT.DEPT WHERE DEPTNO=10
TAB_COL_RECS = @@EMP##DEPTNO@@DEPT##DEPTNO
Process exited.
Disconnecting from the database test.
정확히 일치하는 항목을 찾는 대신 LIKE 쿼리를 사용하여 대소 문자를 구분하지 않고 검색하도록 코드 수정 ...
DECLARE
match_count INTEGER;
-- Type the owner of the tables you want to search.
v_owner VARCHAR2(255) :='USER';
-- Type the data type you're looking for (in CAPS). Examples include: VARCHAR2, NUMBER, etc.
v_data_type VARCHAR2(255) :='VARCHAR2';
-- Type the string you are looking for.
v_search_string VARCHAR2(4000) :='Test';
BEGIN
dbms_output.put_line( 'Starting the search...' );
FOR t IN (SELECT table_name, column_name FROM all_tab_cols where owner=v_owner and data_type = v_data_type) LOOP
EXECUTE IMMEDIATE
'SELECT COUNT(*) FROM '||t.table_name||' WHERE LOWER('||t.column_name||') LIKE :1'
INTO match_count
USING LOWER('%'||v_search_string||'%');
IF match_count > 0 THEN
dbms_output.put_line( t.table_name ||' '||t.column_name||' '||match_count );
END IF;
END LOOP;
END;
@Lalit Kumars 답변에 대해 다음과 같은 문제가 발생했습니다.
ORA-19202: Error occurred in XML processing
ORA-00904: "SUCCESS": invalid identifier
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
해결책은 다음과 같습니다.
WITH char_cols AS
(SELECT /*+materialize */ table_name, column_name
FROM cols
WHERE data_type IN ('CHAR', 'VARCHAR2'))
SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
SUBSTR (table_name, 1, 14) "Table",
SUBSTR (column_name, 1, 14) "Column"
FROM char_cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select "'
|| column_name
|| '" from "'
|| table_name
|| '" where upper("'
|| column_name
|| '") like upper(''%'
|| :val
|| '%'')' ).extract ('ROWSET/ROW/*') ) ) t
ORDER BY "Table"
/
SQL 프롬프트에서 간단한 해결책은 없습니다. 그러나 사용자가 검색 할 문자열을 입력 할 수있는 GUI가있는 toad 및 PL / SQL Developer와 같은 몇 가지 도구가 있지만 이것이 발견 된 테이블 / 프로 시저 / 객체를 반환합니다.
이러한 종류의 검색을 수행하는 몇 가지 무료 도구가 있습니다. 예를 들어,이 도구는 제대로 작동하고 소스 코드는 다음과 같습니다. https://sites.google.com/site/freejansoft/dbsearch
이 도구를 사용하려면 Oracle ODBC 드라이버와 DSN이 필요합니다.
-실행 완료-오류 없음
SET SERVEROUTPUT ON SIZE 100000
DECLARE
v_match_count INTEGER;
v_counter INTEGER;
v_owner VARCHAR2 (255) := 'VASOA';
v_search_string VARCHAR2 (4000) := '99999';
v_data_type VARCHAR2 (255) := 'CHAR';
v_sql CLOB := '';
BEGIN
FOR cur_tables
IN ( SELECT owner, table_name
FROM all_tables
WHERE owner = v_owner
AND table_name IN (SELECT table_name
FROM all_tab_columns
WHERE owner = all_tables.owner
AND data_type LIKE
'%'
|| UPPER (v_data_type)
|| '%')
ORDER BY table_name)
LOOP
v_counter := 0;
v_sql := '';
FOR cur_columns
IN (SELECT column_name, table_name
FROM all_tab_columns
WHERE owner = v_owner
AND table_name = cur_tables.table_name
AND data_type LIKE '%' || UPPER (v_data_type) || '%')
LOOP
IF v_counter > 0
THEN
v_sql := v_sql || ' or ';
END IF;
IF cur_columns.column_name is not null
THEN
v_sql :=
v_sql
|| 'upper('
|| cur_columns.column_name
|| ') ='''
|| UPPER (v_search_string)||'''';
v_counter := v_counter + 1;
END IF;
END LOOP;
IF v_sql is null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name;
END IF;
IF v_sql is not null
THEN
v_sql :=
'select count(*) from '
|| v_owner
|| '.'
|| cur_tables.table_name
|| ' where '
|| v_sql;
END IF;
--v_sql := 'select count(*) from ' ||v_owner||'.'|| cur_tables.table_name ||' where '|| v_sql;
--dbms_output.put_line(v_sql);
--DBMS_OUTPUT.put_line (v_sql);
EXECUTE IMMEDIATE v_sql INTO v_match_count;
IF v_match_count > 0
THEN
DBMS_OUTPUT.put_line (v_sql);
dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'Error when executing the following: '
|| DBMS_LOB.SUBSTR (v_sql, 32600));
END;
/
이 블로그 게시물 에서 차용, 약간 향상 및 단순화 하는 다음과 같은 간단한 SQL 문은 그 일을 잘 수행하는 것 같습니다.
SELECT DISTINCT (:val) "Search Value", TABLE_NAME "Table", COLUMN_NAME "Column"
FROM cols,
TABLE (XMLSEQUENCE (DBMS_XMLGEN.GETXMLTYPE(
'SELECT "' || COLUMN_NAME || '" FROM "' || TABLE_NAME || '" WHERE UPPER("'
|| COLUMN_NAME || '") LIKE UPPER(''%' || :val || '%'')' ).EXTRACT ('ROWSET/ROW/*')))
ORDER BY "Table";
반응형
'IT story' 카테고리의 다른 글
| 기본 저장소를 설정하는 방법 (0) | 2020.08.06 |
|---|---|
| 한 지역 지점을 다른 지역 지점으로 병합 (0) | 2020.08.06 |
| Android-조각 ID 설정 (0) | 2020.08.06 |
| 자바 스크립트의 고유 한 객체 식별자 (0) | 2020.08.06 |
| git add 실행 여부에 관계없이 분기 (수정, 추가, 삭제 된 파일)를 전환 할 때 왜 git에 변경 사항이 계속 표시됩니까? (0) | 2020.08.06 |