Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • OEM-Commnds Oracle
  • Temporary tablespace explaination Oracle
  • column level grant syntax Oracle
  • New OFA for 11g Oracle
  • Generate SSH without password authentication. Linux/Unix
  • 10g RAC: Troubleshooting CRS Root.sh Problems Oracle
  • SAN Linux/Unix
  • tar and untar a dolder with all its subfolder. Linux/Unix
  • standard Monitoring – 1 Oracle
  • Implementing Listener Security Oracle
  • Korn Shell Arithmatic Linux/Unix
  • Explain Plan Doesn’T Change For Sql After New Statistics Generated Oracle
  • Adding a new disk and mount it automatically. on VMWARE LINUX Linux/Unix
  • Import and export statements Oracle
  • login.sql Oracle

fkwoind.sql fkwoindex.sql

Posted on 28-Mar-2006 By Admin No Comments on fkwoind.sql fkwoindex.sql

set pagesize 300

set linesize 205

set head on

set echo off

spool fkwoind

accept usrid prompt ‘Enter User Name : ‘

col “Cons Name” format a30 heading “Cons Name” word_wrapped

col status format a10 heading status

col “Cons Type” format a12 heading “Cons Type”

col “Column Name” format a30 heading “Column Name”

col “Cons Desc” format a50 heading “Cons Desc” word_wrapped

REM Find all FK Constraints, that does not have column position in constraint and in index matching

select /*+ rule */ b.table_name, b.constraint_name “FK-Cons|Name”,

substr(a.column_name, 1, 30) “Column Name”, a.position, b.status

from dba_cons_columns a, dba_constraints b

where a.constraint_name = b.constraint_name

and a.owner = b.owner

and a.owner = upper(‘&usrid’)

and b.constraint_type = ‘R’

and (b.table_name, a.column_name, a.position) not in

( select table_name, column_name, column_position

from dba_ind_columns

where table_owner = a.owner

)

order by b.table_name, b.constraint_name, a.position;

REM Find all FK Constraints, that does not have index on it.

select /*+ rule */ b.table_name, b.constraint_name “FK-Cons|Name”,

substr(a.column_name, 1, 30) “Column Name”, b.status

from dba_cons_columns a, dba_constraints b

where a.constraint_name = b.constraint_name

and a.owner = b.owner

and a.owner = upper(‘&usrid’)

and b.constraint_type = ‘R’

and (b.table_name, a.column_name) not in

( select table_name, column_name

from dba_ind_columns

where table_owner = a.owner

)

order by b.table_name, b.constraint_name, a.position;

spool off

Oracle, SQL scripts

Post navigation

Previous Post: usnsql.sql Displays information about UNDO segments with sql statements
Next Post: tblwopk.sql tablewopk.sql

Related Posts

  • V$transaction notes for finding XID composition. Oracle
  • _B_TREE_BITMAP_PLANS issue during 8.1.7 to 9.2.0.8 upgrade Oracle
  • Locally Managed Tablespace and Dictionary managed tablespace (LMT-DMT) Oracle
  • myfile Oracle
  • Metalink Note: Note:250655.1 : ADDM Basics USING THE AUTOMATIC DATABASE DIAGNOSTIC MONITOR Oracle
  • V$ROLLSTAT status is Full Oracle

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • AWS (2)
  • Azure (1)
  • Linux/Unix (149)
  • Oracle (392)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (341)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • load SPM baseline from cursor cache05-Jun-2025
  • Drop all SPM baselines for SQL handle05-Jun-2025
  • Load SPM baseline from AWR05-Jun-2025
  • Drop specific SQL plan baseline – spm05-Jun-2025
  • findinfo.sql (SQL for getting CPU and Active session info)27-May-2025
  • SQL Tracker by SID sqltrackerbysid.sql22-Apr-2025
  • How to connect to Oracle Database with Wallet with Python.21-Mar-2025
  • JSON/XML Types in Oracle18-Mar-2025
  • CPU Core related projections12-Mar-2025
  • Exadata Basics10-Dec-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • move_arch_files.ksh Linux/Unix
  • fkwoindex.sql /* Find FK without Index */ Oracle
  • longtx.sql Oracle
  • Default User Profile Oracle
  • 276434.1 Modifying the VIP or VIP Hostname of a 10g or 11g Oracle Clusterware Node Oracle
  • find_log_switch.sql Find log switches in graphical manner Oracle
  • RAC 11g with vmware Oracle
  • Reading config file from other folder inside class Python/PySpark

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme