http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [64]
02.File Book
File 자료실 [93]
Site Link [15]
개인폴더 [184]
03.Field Book
Altibase [15]
Tibero [22]
MS-SQL [17]
MySQL [33]
DB2 [75]
ORACLE [2493]
PostgreSQL [73]
기타정보 [136]
운영체제 [545]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Field Book :: ORACLE

 회원가입

v$sql v$db_object_cache v$object_dependency dba_dependencies [ grant 정보, SYNONYM , object 연관성 확인 , SQL query 연관성 조사, grant 권한 연결 참조 ]
최길호 [LIST]   2020-06-26 17:27:51, 조회 : 2,669



추가 update: http://iotn.co.kr/power/zboard.php?id=9_1_bbs&no=436

-- 고객사 DBA 권한 제거 요청으로 정보 확인.

-- object 연관성 확인 [ grnat 정보 ]
col owner for a10
col referenced_owner for a10
col name for a40
col ref_name for a40
col referenced_name for a40
col type for a15
col ref_type for a15
col referenced_type for a20
set lines 200 pages 8000
col db_link for a20
-- ref_name : grant ... on ref_name to owner
SELECT owner||'.'||name name,type,referenced_owner||'.'||referenced_name ref_name,referenced_type ref_type,REFERENCED_LINK_NAME db_link
FROM dba_dependencies
where      owner not in ('SYS','PUBLIC') and
referenced_owner not in (select username from dba_users where
created <= ( select max(created) from dba_users where username in ( 'MGMT_VIEW','ORACLE_OCM')) union all select 'PUBLIC' from dual)
and referenced_type not in ('NON-EXISTENT') and owner != referenced_owner order by 1,2 ;

-- db link REFERENCED_LINK_NAME  확인
select a.owner||'.'||object_name name,object_type type from dba_objects a, dba_dependencies b
where object_name = REFERENCED_LINK_NAME ;



@?/rdbms/admin/utldtree.sql
select 'execute deptree_fill('''||referenced_type||''','''||referenced_owner||''','''||referenced_name||''');'
||chr(10)||'select * from deptree order by seq#;'
||chr(10)||'select * from ideptree;'
FROM dba_dependencies
where referenced_owner not in ('SYS','SYSTEM','SYSMAN','PUBLIC','WMSYS','DBSNMP','OUTLN')
and referenced_type not in ('NON-EXISTENT') and owner != referenced_owner;


-- SQL Query 연관성 확인
drop table vdb_object_cache;
drop table vsql ;
drop table vobject_dependency;
drop table sql2tmp ;

create global temporary table vdb_object_cache
on commit preserve rows as
-- insert into vdb_object_cache
select name,type,owner
from v$db_object_cache
where type not in ('NON-EXISTENT','NOT LOADED','PUB_SUB')
and owner not in ('SYS','SYSTEM','PUBLIC');

create global temporary table vsql
on commit preserve rows as
-- insert into vsql
select address,command_type,PARSING_SCHEMA_NAME from v$sql
where PARSING_SCHEMA_NAME not in ('SYS','SYSTEM');

create global temporary table vobject_dependency
on commit preserve rows as
-- insert into vobject_dependency
select from_address,to_owner,to_name from v$object_dependency;

create global temporary table sql2tmp (sql varchar2(120)) on commit preserve rows ;
--
insert into sql2tmp
select distinct 'grant '|| case c.type when 'TABLE' then d.name else 'execute' end
||' on '||owner||'.'||c.name||' to '||PARSING_SCHEMA_NAME||';' sql
from vsql a, vobject_dependency b, vdb_object_cache  c, audit_actions d
where b.from_address=a.address  and command_type=d.action and
b.to_owner = c.owner and b.to_name = c.name and owner != PARSING_SCHEMA_NAME
order by 1;

-- 하루에 한번씩 실행 하면 모일 것이다. full scan 하면 마지막 SQL들이 최근 추가된 SQL이다.
create table sql2grant (sql varchar2(120));
--
insert into sql2grant
select * from sql2tmp
minus
select * from sql2grant;

-- 확인
select * from sql2grant;



col sqltext for a40
col owner for a10
col sql_user for a10
col name for a25
select distinct owner,c.name,c.type,PARSING_SCHEMA_NAME sql_user,d.name sql_type
from vsql a, vobject_dependency b, vdb_object_cache  c, audit_actions d
where b.from_address=a.address  and command_type=d.action and
b.to_name = c.name and owner != PARSING_SCHEMA_NAME
order by 1,2,3;

-- gent grnat sql
col sql for a120
select distinct 'grant '|| case c.type when 'TABLE' then d.name else 'execute' end
||' on '||owner||'.'||c.name||' to '||PARSING_SCHEMA_NAME||';' sql
from vsql a, vobject_dependency b, vdb_object_cache  c, audit_actions d
where b.from_address=a.address  and command_type=d.action and
b.to_name = c.name and owner != PARSING_SCHEMA_NAME
order by 1;


  LIST

제목 작성자 작성일 조회
Admin  진행중.    최길호 2008/11/26 105
Admin  [ 필요 하신게 있으시면 Login 하시고 쪽지 또는 gilho.kr@gmail.com 메일 주세요~ ]    최길호 2006/07/14 122
Troubleshoot  ORA-02291: integrity constraint / ORA-02292: integrity constraint / ORA-02449: unique/primary keys / ORA-02298: cannot validate [ 제약조건 ]    최길호 2020/07/14 13
Admin  데이터 유지 ASM 재설치 test [ asmcmd md_backup ]    최길호 2020/07/13 3
Troubleshoot  PLS-00103: Encountered the symbol / PLS-00201: identifier    최길호 2020/07/08 10
Troubleshoot  ADVM Driver Not installed / ORA-15032 ORA-15489 delete waiting for last close    최길호 2020/07/08 30
Troubleshoot  dba_scheduler_running_jobs session_id is null    최길호 2020/07/05 17
Script  linux config, dbca, create tablespace, expdp, rman backup script    최길호 2020/07/02 29
Troubleshoot  PLS-00302: component 'table' must be declared    최길호 2020/07/02 32
Admin  dba role create view ORA-01031: insufficient privileges    최길호 2020/06/28 63
Admin  v$sql v$db_object_cache v$object_dependency dba_dependencies [ grant 정보, SYNONYM , object 연관성 확인 , SQL query 연관성 조사, grant 권한 연결 참조 ]    최길호 2020/06/26 2669
Admin  dbms_scheduler 사용 [ LOG 1년6개월 보관 ] exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('log_history','550');    최길호 2020/06/18 137
Troubleshoot  ORA-01157: cannot identify/lock data file 77 - see DBWR trace file 조치 예    최길호 2020/06/15 108
Troubleshoot  ORA-04063: view SYS.DBA_HIST_SQL_PLAN has errors    최길호 2020/05/28 121
Backup and Recovery  rman cdb pdb backup / restore / recover    최길호 2020/05/28 126
Backup and Recovery  impdp system/<pwd> ... remap_table=EMP_INFO:EMP_INFO_0525 tables=scott.emp_info    최길호 2020/05/26 148

    목록보기   다음페이지 1 [2][3][4][5][6][7][8][9][10]..[125]   [다음 10개]
       

Copyright 1999-2020 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2019/05/02
pkg 확인 SELECT * FROM AL...
by 최길호
2018/05/18
Name Type ...
by 최길호
2018/01/07
ORA-00020: maximum number of...
by 최길호
최근 게시물
07/16
[운영체제]
docker search / rename / commi....
by 최길호
07/15
[운영체제]
docker login / oracle 12.2.
by 최길호
07/14
[ORACLE]
ORA-02291: integrity constrain....
by 최길호