Skip to content
pvmehta.com

pvmehta.com

  • Home
  • About Me
  • Toggle search form
  • Locktree.sql Oracle
  • Changing default shell Linux/Unix
  • checking redhat linux version Linux/Unix
  • Best approach for Oracle database patching sequence to latest/required patchset along with CPU/PSU/any-other-one-off patch ID 865255.1 Oracle
  • export import with parameter file. Oracle
  • How to Decide upto what level you can decrement your datafile size. ( Shrink Datafile) Oracle
  • Good Link from metalink 1 Oracle
  • cp_filesystem.sql Oracle
  • Export Import with QUERY Oracle
  • All About oracle password and security from metalink Oracle
  • Good links for x$ tables in oracle. Oracle
  • sess1.sql Oracle
  • Rman Notes -1 Oracle
  • get_vmstat_solaris Oracle
  • Free conference number from http://www.freeconference.com 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

  • How to Use DBMS_STATS to Move Statistics to a Different Database Oracle
  • v$backup.status information Oracle
  • Mutating Table Error while using database trigger Oracle
  • 751131.1 New Article Error 2819 While Requesting a Systemstate Dump Oracle
  • Another Tuning Article for subheap of shared pool Oracle
  • Oracle Material from OTN 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 (388)
  • PHP/MYSQL/Wordpress (10)
  • Power-BI (0)
  • Python/PySpark (7)
  • RAC (17)
  • rman-dataguard (26)
  • shell (149)
  • SQL scripts (337)
  • Uncategorized (0)
  • Videos (0)

Recent Posts

  • 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
  • Reading config file from other folder inside class24-Sep-2024
  • Python class import from different folders22-Sep-2024
  • Transfer SQL Profiles from One database to other database.05-Sep-2024
  • Load testing on Oracle 19C RAC with HammerDB18-Jan-2024

Archives

  • 2025
  • 2024
  • 2023
  • 2010
  • 2009
  • 2008
  • 2007
  • 2006
  • 2005
  • Configure ssh authentications for RAC Oracle
  • move_arch_files.ksh Linux/Unix
  • Exadata Basics Oracle
  • remove archfiles only when it is applied to DR rm_archfiles.sh Linux/Unix
  • 10g oem configuration Oracle
  • move_arch_files.ksh Linux/Unix
  • shr1.sql for MTS or Shared server configuration Oracle
  • v$event_name Oracle

Copyright © 2025 pvmehta.com.

Powered by PressBook News WordPress theme