www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   회원:0명 / 손님:0명
 

 

Technote

자료 및 관리팁

  • DBA Notes
  • Q & A

    북마크
  • Asktom
       (Oracle 의 대표 Forum)
  • Technical Bulltin(KR)
       (기술지원게시판)
  • Dbazine

  •  

     


     Unindex Foreign Key List..
    park1q  2009-06-15 14:22:40, 조회 : 2,384, 추천 : 747


    select a.owner, decode( b.table_name, NULL, '****', 'ok' ) Status,
             a.table_name, a.constraint_name,  a.columns fk_columns, b.columns ind_columns
      from (select b.owner , 
                   substr(a.table_name,1,30) table_name,
                   substr(a.constraint_name,1,30) constraint_name,
                   max(decode(position, 1,     substr(column_name,1,30),NULL)) ||
                   max(decode(position, 2,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 3,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 4,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 5,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 6,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 7,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 8,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position, 9,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,10,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,11,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,12,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,13,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,14,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,15,', '||substr(column_name,1,30),NULL)) ||
                   max(decode(position,16,', '||substr(column_name,1,30),NULL)) columns
       from dba_cons_columns a, dba_constraints b
      where a.constraint_name = b.constraint_name
        and b.constraint_type = 'R'
        and b.owner = 'WINS'
        and a.owner = b.owner
      group by b.owner , substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,
            (select index_owner owner, substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,
                    max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 2,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 3,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 4,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 5,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 6,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 7,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 8,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position, 9,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,10,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,11,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,12,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,13,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,14,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,15,', '||substr(column_name,1,30),NULL)) ||
                    max(decode(column_position,16,', '||substr(column_name,1,30),NULL)) columns
               from dba_ind_columns
              where index_owner = 'WINS'
              group by index_owner , substr(table_name,1,30), substr(index_name,1,30) ) b
     where a.table_name = b.table_name (+)
       and a.owner = b.owner(+)
       and b.columns (+) like a.columns || '%'

     
     
    SQL> @unindex

    STAT TABLE_NAME                     COLUMNS              COLUMNS
    ---- ------------------------------ -------------------- --------------------
    **** APPLICATION_INSTANCES          AI_APP_CODE
    ok   EMP                            DEPTNO               DEPTNO


      추천하기 프린트   목록보기

    Copyright 1999-2018 Zeroboard

     

     
     
    [Today:7 / Total:155941]    Design by p@rk1q