Tuesday, 6 September 2011

How does one enable the SQL*Plus HELP facility?

Oracle SQL*Plus help

cd $ORACLE_HOME/sqlplus/admin/help

oracle@apt-amd-02:/opt/app/oracle/product/11.2/sqlplus/admin/help> ls -ltr
total 80
-rw-r--r-- 1 oracle oinstall 337 2000-06-28 01:30 helpdrop.sql
-rw-r--r-- 1 oracle oinstall 265 2003-02-16 20:47 helpbld.sql
-rw-r--r-- 1 oracle oinstall 2086 2009-01-05 20:07 hlpbld.sql
-rw-r--r-- 1 oracle oinstall 65975 2009-06-28 21:54 helpus.sql

oracle@apt-amd-02:/opt/app/oracle/product/11.2/sqlplus/admin/help> sqlplus system/sys @hlpbld.sql helpus.sql

select info
from system.help
where upper(topic)=upper('&1')
/

Enter value for 1: COLUMN
old 1: select info from system.help where upper(topic)=upper('&1')
new 1: select info from system.help where upper(topic)=upper('COLUMN')

INFO
--------------------------------------------------------------------------------

COLUMN
------

Specifies display attributes for a given column, such as:
- text for the column heading
- alignment for the column heading
- format for NUMBER data
- wrapping of column data
Also lists the current display attributes for a single column
or all columns.

COL[UMN] [{column | expr} [option ...] ]

where option represents one of the following clauses:
ALI[AS] alias
CLE[AR]
ENTMAP {ON|OFF}
FOLD_A[FTER]
FOLD_B[EFORE]
FOR[MAT] format
HEA[DING] text
JUS[TIFY] {L[EFT] | C[ENTER] | R[IGHT]}
LIKE {expr | alias}
NEWL[INE]
NEW_V[ALUE] variable
NOPRI[NT] | PRI[NT]
NUL[L] text
OLD_V[ALUE] variable
ON|OFF
WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

32 rows selected.

Oracle Table size

Table Size !!!!



DEFINE schema_name = 'ANUJ'
set pagesize 500 linesize 300

col OBJECT_NAME for a30
col TABLE_NAME for a30 
col gb for 999999.99


SELECT distinct owner,table_name,total_table_meg/1000 GB FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, tablespace_name, extents, initial_extent,  ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/




 --- with database name 
 DEFINE schema_name = 'ANUJ'  -----!!!!!!
set pagesize 500 linesize 300

col OBJECT_NAME for a30
col TABLE_NAME for a25 
col gb for 999999.99
col owner for a20
col INSTANCE_NAME for a14
col DB_NAME 	for a12
col SERVER_HOST for a15
SELECT distinct  sys_context('USERENV','INSTANCE_NAME') INSTANCE_NAME ,sys_context('USERENV','DB_NAME') DB_NAME, sys_context('USERENV','SERVER_HOST') SERVER_HOST ,owner,table_name,total_table_meg/1000 GB FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg, tablespace_name, extents, initial_extent,  ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/







with dbms_xplan.format_size  ******

DEFINE schema_name ='ARC'
col size1 for a10
col OWNER for a20
col TABLE_NAME for a20
SELECT distinct owner,table_name,dbms_xplan.format_size(total_table) size1 FROM (
--SELECT distinct owner,table_name,total_table_meg MB FROM (
  SELECT
    owner, object_name, object_type, table_name, ROUND(bytes) AS bytes1, tablespace_name, extents, initial_extent,  Sum(bytes) OVER (PARTITION BY table_name) AS total_table
  FROM (
    -- Tables
    SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,segment_name AS table_name, bytes, tablespace_name, extents, initial_extent
    FROM   dba_segments
    WHERE  segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
    UNION ALL
    -- Indexes
    SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type, i.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_indexes i, dba_segments s
    WHERE  s.segment_name = i.index_name
    AND    s.owner = i.owner
    AND    s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
    -- LOB Segments
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type, l.table_name, s.bytes, s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.segment_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBSEGMENT'
    -- LOB Indexes
    UNION ALL
    SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type, l.table_name, s.bytes,s.tablespace_name, s.extents, s.initial_extent
    FROM   dba_lobs l, dba_segments s
    WHERE  s.segment_name = l.index_name
    AND    s.owner = l.owner
    AND    s.segment_type = 'LOBINDEX'
  )
  WHERE owner in UPPER('&schema_name')
)
WHERE 0=0  
-- and total_table_meg > 10
--ORDER BY total_table_meg DESC, meg DESC
/

col topseg_seg_owner HEAD OWNER FOR A15
col topseg_segment_name head SEGMENT_NAME for a30
col topseg_segment_type head SEGMENT_TYPE for a30
col OWNER for a15
col Size1 for a10


define 1='USER'  ---- tablespace NAME

select * from (
    select
        tablespace_name,
        owner,
        segment_name topseg_segment_name,
        --partition_name,
        REPLACE(segment_type, ' PARTITION', ' - PARTITIONED') topseg_segment_type,
        count(*),
        dbms_xplan.format_size(sum(bytes)) Size1
    from dba_segments
    where upper(tablespace_name) like upper('%&1%')
   and owner= 'SYS'     -----<<<<<
    group by tablespace_name, owner, segment_name, segment_type
    order by Size1 desc
)
where rownum <= 30;


set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15
col ByteH for a12
compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB,dbms_xplan.format_size(sum(bytes)) ByteH
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='OOOOOO'
and table_name='OOOOOOOO'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

---- Partition included  !!!!!


set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15
col ByteH for a12
compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB,dbms_xplan.format_size(sum(bytes)) ByteH
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
union all
SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='OOO'
and table_name='OOO'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

set linesize 300
column table_name   format a25
column object_name  format a32
column owner        format a15

compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='XX'
and table_name='XX'
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

***************************

--with regexp_like

set linesize 300
column table_name   format a30
column object_name  format a32
column owner        format a15

compute sum of Size_GB on report
break on report
SELECT
owner, table_name,segment_type,TABLESPACE_NAME, TRUNC(sum(bytes)/1024/1024/1024) Size_GB
FROM
(SELECT segment_name table_name, owner, TABLESPACE_NAME,bytes ,segment_type FROM dba_segments  
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name 
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner,s.TABLESPACE_NAME, s.bytes ,segment_type FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX'
)
WHERE 1=1
and owner ='XXXX'
and regexp_like (table_name, '[0-9]') --- tablename with digit 
GROUP BY owner,table_name,segment_type,TABLESPACE_NAME
ORDER BY SUM(bytes) desc
/

***********************************************

set linesize 300
col SEGMENT_NAME for a20
define table_owner='USER'
WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   --AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name
SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/1024/1024/1024) AS GB, tablespace_name,s.segment_name
  FROM s
 GROUP BY oby, segment_type, tablespace_name,s.segment_name
 ORDER BY oby, segment_type, tablespace_name,s.segment_name
/





---- with dbms_xplan.format_size

set linesize 300
col SEGMENT_NAME for a27
col size1 for a10

define table_owner='ESS'
define table_name='LOG'


WITH
t AS (
SELECT owner, table_name
  FROM dba_tables
 WHERE owner = '&&table_owner.'
   AND table_name = '&&table_name.'
),
s AS (
SELECT 1 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_segments s
 WHERE s.owner = t.owner
   AND s.segment_name = t.table_name
   AND s.segment_type LIKE 'TABLE%'
 UNION ALL
SELECT 2 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, NULL AS column_name, s.bytes, s.tablespace_name
  FROM t, dba_indexes i, dba_segments s
 WHERE i.table_owner = t.owner
   AND i.table_name = t.table_name
   AND s.owner = i.owner
   AND s.segment_name = i.index_name
   AND s.segment_type LIKE 'INDEX%'
 UNION ALL
SELECT 3 AS oby, s.segment_type, s.owner, s.segment_name, s.partition_name, l.column_name, s.bytes, s.tablespace_name
  FROM t, dba_lobs l, dba_segments s
 WHERE l.owner = t.owner
   AND l.table_name = t.table_name
   AND s.owner = l.owner
   AND s.segment_name = l.segment_name
   AND s.segment_type LIKE 'LOB%'
)
--SELECT segment_type, COUNT(*) AS segments, ROUND(SUM(bytes)/POWER(2,20),3) AS mebibytes, tablespace_name
SELECT segment_type, COUNT(*) AS segments, dbms_xplan.format_size(SUM(bytes)) size1, tablespace_name,s.segment_name
  FROM s
 GROUP BY oby, segment_type, tablespace_name,s.segment_name
 ORDER BY oby, segment_type, tablespace_name,s.segment_name
/

all Table size in a particular  schema  
oracle table size 



undefine ownr
accept ownr prompt  'Enter schemaname or press  for all schemas: '
SET LINES 132
SET VERIFY off
COLUMN OWNER FORMAT A30
COLUMN TABLE FORMAT A30
COLUMN Taille FORMAT A15
COLUMN TABLESPACE FORMAT A20
SELECT "OWNER"
,      "TABLE"
,      "DB Blocks"
,      ROUND(DECODE(SIGN("Size"/1048576 -1 )
              , -1 , DECODE(SIGN("Size"/1024 -1)
                     , -1, "Size"
                     ,  "Size"/1024)
             , "Size"/1048576) ,2) "SIZE"
,       DECODE(SIGN("Size"/1048576 -1 )
              , -1, DECODE(SIGN("Size"/1024 -1)
                    ,-1 ,' Byte'
                    , ' Kb')
              , ' Mb') " "
,      "TABLESPACE"
FROM
(SELECT owner "OWNER"
 , segment_name "TABLE"
 , SUM(BYTES)   "Size"
 , blocks "DB Blocks"
 , tablespace_name "TABLESPACE"
 FROM DBA_SEGMENTS
 WHERE segment_type = 'TABLE'
  AND DECODE('&&ownr', null,'X', OWNER) = DECODE('&&ownr',null,'X',UPPER('&&ownr'))
  AND OWNER NOT IN ('SYS' , 'SYSTEM')
 GROUP BY owner, segment_name, tablespace_name, blocks
 ORDER BY owner, segment_name) ;




SQL> @tablesize
Enter schemaname or press  for all schemas: SCOTT


OWNER                          TABLE                           DB Blocks       SIZE       TABLESPACE
------------------------------ ------------------------------ ---------- ---------- ----- --------------------
SCOTT                          DEPT                                    8         64  Kb   USERS
SCOTT                          EMP                                     8         64  Kb   USERS
SCOTT                          SALGRADE                                8         64  Kb   USERS

3 rows selected.







Table size .....


undefine ownr
undefine seg_name
accept ownr prompt  'Enter schemaname or press  for all schemas: '
SET LINES 132
SET VERIFY off
COLUMN OWNER FORMAT A30
COLUMN TABLE FORMAT A30
COLUMN Taille FORMAT A15
COLUMN TABLESPACE FORMAT A20
SELECT "OWNER"
,      "TABLE"
,      "DB Blocks"
,      ROUND(DECODE(SIGN("Size"/1048576 -1 )
              , -1 , DECODE(SIGN("Size"/1024 -1)
                     , -1, "Size"
                     ,  "Size"/1024)
             , "Size"/1048576) ,2) "SIZE"
,       DECODE(SIGN("Size"/1048576 -1 )
              , -1, DECODE(SIGN("Size"/1024 -1)
                    ,-1 ,' Byte'
                    , ' Kb')
              , ' Mb') " "
,      "TABLESPACE"
FROM
(SELECT owner "OWNER"
 , segment_name "TABLE"
 , SUM(BYTES)   "Size"
 , blocks "DB Blocks"
 , tablespace_name "TABLESPACE"
FROM DBA_SEGMENTS
WHERE segment_type = 'TABLE'
AND DECODE('&&ownr', null,'X', OWNER) = DECODE('&&ownr',null,'X',UPPER('&&ownr'))
and segment_name =upper('&Tab_Name') 
AND OWNER NOT IN ('SYS' , 'SYSTEM')
GROUP BY owner, segment_name, tablespace_name, blocks
ORDER BY owner, segment_name) ;


SQL> @tablesize1
Enter schemaname or press  for all schemas: SCOTT
Enter value for tab_name: EMP


OWNER                          TABLE                           DB Blocks       SIZE       TABLESPACE
------------------------------ ------------------------------ ---------- ---------- ----- --------------------
SCOTT                          EMP                                     8         64  Kb   USERS

====================

-- from Web

 
 object size !!!!!!!!!!!!!!!!!!!!!!!
 
 

set linesize 500 pagesize 300

COLUMN name NEW_VALUE _instname NOPRINT
select lower(instance_name) name from v$instance;

COLUMN conname NEW_VALUE _conname NOPRINT
select case 
            when a.conname = 'CDB$ROOT'   then 'ROOT'
            when a.conname = 'PDB$SEED'   then 'SEED'
            else a.conname
            end as conname
from (select SYS_CONTEXT('USERENV', 'CON_NAME') conname from dual) a;

COLUMN conid NEW_VALUE _conid NOPRINT
select SYS_CONTEXT('USERENV', 'CON_ID') conid from dual;



set numf 99999999999999.99
col OWNER for a20
col SEGMENT_NAME for a30
col TABLE_NAME  for a25

WITH schema_object AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       segment_type,
       owner,
       segment_name,
       tablespace_name,
       COUNT(*) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes
  FROM dba_segments
 WHERE 'Y' = 'Y'
 GROUP BY
       segment_type,
       owner,
       segment_name,
       tablespace_name
), totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes
  FROM schema_object
), top_200_pre AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       ROWNUM rank, v1.*
       FROM (
SELECT so.segment_type,
       so.owner,
       so.segment_name,
       so.tablespace_name,
       so.segments,
       so.extents,
       so.blocks,
       so.bytes,
       ROUND((so.segments / t.segments) * 100, 3) segments_perc,
       ROUND((so.extents / t.extents) * 100, 3) extents_perc,
       ROUND((so.blocks / t.blocks) * 100, 3) blocks_perc,
       ROUND((so.bytes / t.bytes) * 100, 3) bytes_perc
  FROM schema_object so,
       totals t
 ORDER BY
       bytes_perc DESC NULLS LAST
) v1
 WHERE ROWNUM < 201
), top_200 AS (
SELECT p.*,
       (SELECT object_id
          FROM dba_objects o
         WHERE o.object_type = p.segment_type
           AND o.owner = p.owner
           AND o.object_name = p.segment_name
           AND o.object_type NOT LIKE '%PARTITION%') object_id,
       (SELECT data_object_id
          FROM dba_objects o
         WHERE o.object_type = p.segment_type
           AND o.owner = p.owner
           AND o.object_name = p.segment_name
           AND o.object_type NOT LIKE '%PARTITION%') data_object_id,
       (SELECT SUM(p2.bytes_perc) FROM top_200_pre p2 WHERE p2.rank <= p.rank) bytes_perc_cum
  FROM top_200_pre p
), top_200_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
), top_100_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
 WHERE rank < 101
), top_20_totals AS (
SELECT /*+  MATERIALIZE NO_MERGE  */ /* 2b.207 */
       SUM(segments) segments,
       SUM(extents) extents,
       SUM(blocks) blocks,
       SUM(bytes) bytes,
       SUM(segments_perc) segments_perc,
       SUM(extents_perc) extents_perc,
       SUM(blocks_perc) blocks_perc,
       SUM(bytes_perc) bytes_perc
  FROM top_200
 WHERE rank < 21
)
SELECT v.rank,
       v.segment_type,
       v.owner,
       v.segment_name,
       v.object_id,
       v.data_object_id,
       v.tablespace_name,
       CASE
       WHEN v.segment_type LIKE 'INDEX%' THEN
         (SELECT i.table_name
            FROM dba_indexes i
           WHERE i.owner = v.owner AND i.index_name = v.segment_name)
       WHEN v.segment_type LIKE 'LOB%' THEN
         (SELECT l.table_name
            FROM dba_lobs l
           WHERE l.owner = v.owner AND l.segment_name = v.segment_name)
       ELSE v.segment_name
       END table_name,
       v.segments,
       v.extents,
       v.blocks,
       v.bytes,
       ROUND(v.bytes / POWER(10,9), 3) gb,
       LPAD(TO_CHAR(v.segments_perc, '990.000'), 7) segments_perc,
       LPAD(TO_CHAR(v.extents_perc, '990.000'), 7) extents_perc,
       LPAD(TO_CHAR(v.blocks_perc, '990.000'), 7) blocks_perc,
       LPAD(TO_CHAR(v.bytes_perc, '990.000'), 7) bytes_perc,
       LPAD(TO_CHAR(v.bytes_perc_cum, '990.000'), 7) perc_cum
  FROM (
SELECT d.rank,
       d.segment_type,
       d.owner,
       d.segment_name,
       d.object_id,
       d.data_object_id,
       d.tablespace_name,
       d.segments,
       d.extents,
       d.blocks,
       d.bytes,
       d.segments_perc,
       d.extents_perc,
       d.blocks_perc,
       d.bytes_perc,
       d.bytes_perc_cum
  FROM top_200 d
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP  20' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_20_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP 100' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_100_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOP 200' tablespace_name,
       st.segments,
       st.extents,
       st.blocks,
       st.bytes,
       st.segments_perc,
       st.extents_perc,
       st.blocks_perc,
       st.bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM top_200_totals st
 UNION ALL
SELECT TO_NUMBER(NULL) rank,
       NULL segment_type,
       NULL owner,
       NULL segment_name,
       TO_NUMBER(NULL),
       TO_NUMBER(NULL),
       'TOTAL' tablespace_name,
       t.segments,
       t.extents,
       t.blocks,
       t.bytes,
       100 segemnts_perc,
       100 extents_perc,
       100 blocks_perc,
       100 bytes_perc,
       TO_NUMBER(NULL) bytes_perc_cum
  FROM totals t) v;
  
  
  
               RANK SEGMENT_TYPE       OWNER                SEGMENT_NAME                            OBJECT_ID     DATA_OBJECT_ID TABLESPACE_NAME                TABLE_NAME                      SEGMENTS             EXTENTS             BLOCKS              BYTES                 GB SEGMENTS_PERC                EXTENTS_PERC                 BLOCKS_PERC                  BYTES_PERC       PERC_CUM
------------------ ------------------ -------------------- ------------------------------ ------------------ ------------------ ------------------------------ ------------------------- ------------------ ------------------ ------------------ ------------------ ------------------ ---------------------------- ---------------------------- ---------------------------- ---------------------------- ----------------------------
              1.00 TABLE              OT                   T                                       833695.00          833695.00 USERS                          T                                   1.00              412.00         1476480.00     12095324160.00              12.10    0.01                         1.62                        33.52                        33.52            33.52
              2.00 TABLE              TEST2                T                                       582806.00          582814.00 USERS                          T                                   1.00              241.00          475136.00      3892314112.00               3.89    0.01                         0.94                        10.78                        10.78            44.31
              3.00 TABLE              ANUJ                 TEST9                                   206819.00          206819.00 USERS                          TEST9                               1.00              232.00          401408.00      3288334336.00               3.29    0.01                         0.91                         9.11                         9.11            53.42
              4.00 INDEX              SYS                  I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST           14364.00           14364.00 SYSAUX                         WRI$_OPTSTAT_HISTGRM_HIST           1.00              244.00          172672.00      1414529024.00               1.42    0.01                         0.96                         3.92                         3.92            57.34
                                                                                                                                                               ORY

==========

----schema size

https://anuj-singh.blogspot.com/2023/

Monday, 5 September 2011

Oracle Count alphabet

count in plsql

DECLARE
l_count_a NUMBER := 0;
l_string VARCHAR2(200);
l_string_length INTEGER;
BEGIN
l_string := 'eyryoeryaaasdflhaaaasdfhfhhhjaaasdfaffdsfaadsfdooo83u4384asdfaaaassssaaddAAAAA';

l_string_length := LENGTH( l_string );

FOR i IN 1..l_string_length LOOP
IF SUBSTR( l_string, i, 1 ) IN ( 'A', 'a' ) THEN
l_count_a := l_count_a + 1;
END IF;
END LOOP;

dbms_output.put_line( 'aA Count: ' || l_count_a );
END;
/

aA Count: 25

Saturday, 3 September 2011

encrypted data in Oracle

How to store data DES encrypted in Oracle ?

This tip is from the Oracle Magazine, it shows the usage of the DBMS_OBFUSCATION_TOOLKIT.
The DBMS_OBFUSCATION_TOOLKIT is the DES encryption package. This package shipped with Oracle8i Release 2 and later. It provides first-time field-level encryption in the database. The trick to using this package is to make sure everything is a multiple of eight. Both the key and the input data must have a length divisible by eight (the key must be exactly 8 bytes long).

Example

CREATE OR REPLACE PROCEDURE obfuscation_demo AS
l_data varchar2(255);
l_string VARCHAR2(25) := 'hello world';
BEGIN
--
-- Both the key and the input data must have a length
-- divisible by eight (the key must be exactly 8 bytes long).
--
l_data := RPAD(l_string,(TRUNC(LENGTH(l_string)/8)+1)*8,CHR(0));
--
DBMS_OUTPUT.PUT_LINE('l_string before encrypt: ' || l_string);
--
-- Encrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT
(input_string => l_data,
key_string => 'magickey',
encrypted_string => l_string);
--
DBMS_OUTPUT.PUT_LINE('l_string ENCRYPTED: ' || l_string);
--
--
-- Decrypt the input string
--
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT
(input_string => l_string,
key_string => 'magickey',
decrypted_string => l_data);
--
DBMS_OUTPUT.PUT_LINE('l_string DECRYPT: ' || L_DATA);
--
END;
/

SQL> exec obfuscation_demo

l_string before encrypt: hello world
l_string ENCRYPTED: ¿¿¿H?¿¿¿
l_string DECRYPT: hello world

PL/SQL procedure successfully completed.

You must protect and preserve your "magickey"—8 bytes of data that is used to encrypt/decrypt the data. If it becomes compromised, your data is vulnerable.

Oracle foreign key constraints procedure

CREATE OR REPLACE
PROCEDURE show_fkeys(
p_table_name IN user_constraints.table_name%TYPE)
IS
-- constants
-- identify fkeys on pkey for given table
CURSOR id_fkeys (
c_table_name user_constraints.table_name%TYPE)
IS
SELECT table_name, constraint_name fkey, r_constraint_name pkey,
status
FROM user_constraints
WHERE constraint_type='R'
AND r_constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE table_name=c_table_name
AND constraint_type='P')
ORDER BY table_name, constraint_name;
-- record variables
rec_id_fkeys id_fkeys%ROWTYPE;
-- variables
l_table_name user_constraints.table_name%TYPE;
l_pkey_name user_constraints.constraint_name%TYPE;
l_status NUMERIC;
BEGIN
l_table_name := UPPER(p_table_name);
-- a primary key for the given table must exist
SELECT constraint_name
INTO l_pkey_name
FROM user_constraints
WHERE table_name=l_table_name
AND constraint_type='P';
DBMS_OUTPUT.put_line(
'show_fkeys: foreign key constraints on table [' || l_table_name || ']');
DBMS_OUTPUT.put_line(
' whose primary key is [' || l_pkey_name || ']');
OPEN id_fkeys(l_table_name);
LOOP -- display foreign keys
FETCH id_fkeys INTO rec_id_fkeys;
EXIT WHEN id_fkeys%NOTFOUND;
DBMS_OUTPUT.put_line(RPAD('Table: [' || rec_id_fkeys.table_name || ']',40) ||
RPAD('FK Name: [' || rec_id_fkeys.fkey || ']',42) ||
'Status: [' || rec_id_fkeys.status || ']');
END LOOP; -- display foreign keys
IF (id_fkeys%ROWCOUNT = 0) THEN -- no fkeys found
DBMS_OUTPUT.put_line(
'show_fkeys: No foreign keys found against table ' || l_table_name);

END IF; -- no rows found
CLOSE id_fkeys;
EXCEPTION
WHEN NO_DATA_FOUND THEN -- primary key lookup failed
DBMS_OUTPUT.put_line(
'show_fkeys: no primary key exists for table ' || l_table_name);
WHEN OTHERS THEN
l_status := SQLCODE;
DBMS_OUTPUT.put_line('show_fkeys: ' || SQLERRM(l_status));
IF (id_fkeys%ISOPEN) THEN
CLOSE id_fkeys;
END IF;
END show_fkeys;
/

SQL>set serveroutput on

SQL> exec show_fkeys('DEPT');
show_fkeys: foreign key constraints on table [DEPT]
whose primary key is [PK_DEPT]
Table: [EMP] FK Name: [FK_DEPTNO]
Status: [ENABLED]

PL/SQL procedure successfully completed.

Oracle Box ip address on SQLPLUS

Find a ip address of Oracle Box

select sys_context('userenv','ip_address') from dual;

Oracle missing index on foreign key

Oracle : Foreign Keys without Index

Oracle : Foreign Keys without Index
 
 

-- from wrox book chepter 3
-- run this script from schema.............


column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped
select table_name,constraint_name,
cname1 ||nvl2(cname2,','||cname2,null)||
nvl2(cname3,','||cname3,null)||nvl2(cname4,',' ||cname4,null)||
nvl2(cname5,','||cname5,null)||nvl2(cname6,',' ||cname6,null)||
nvl2(cname7,','||cname7,null)||nvl2(cname8,',' ||cname8,null) columns
from(select b.table_name,b.constraint_name,
max(decode(position,1,column_name,null)) cname1,
max(decode(position,2,column_name,null)) cname2,
max(decode(position,3,column_name,null)) cname3,
max(decode(position,4,column_name,null)) cname4,
max(decode(position,5,column_name,null)) cname5,
max(decode(position,6,column_name,null)) cname6,
max(decode(position,7,column_name,null)) cname7,
max(decode(position,8,column_name,null)) cname8,
count(*) col_cnt
from ( select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position from user_cons_columns) a,
user_constraints b
where a.constraint_name=b.constraint_name
and b.constraint_type='R'
group by b.table_name,b.constraint_name) cons
where col_cnt&gt;all (select count(*) from user_ind_columns i
where i.table_name=cons.table_name
and i.column_name in (cname1,cname2,cname3,cname4,cname5,cname6,cname7,cname8)
and i.column_position&lt;=cons.col_cnt
group by i.index_name)
/

========



column columns format a20 word_wrapped
column table_name format a30 word_wrapped

select decode( b.table_name, NULL, '****', 'ok' ) Status,
a.table_name, a.columns, b.columns
from
( select a.table_name, a.constraint_name,
max(decode(position, 1, column_name,NULL)) ||
max(decode(position, 2,', '||column_name,NULL)) ||
max(decode(position, 3,', '||column_name,NULL)) ||
max(decode(position, 4,', '||column_name,NULL)) ||
max(decode(position, 5,', '||column_name,NULL)) ||
max(decode(position, 6,', '||column_name,NULL)) ||
max(decode(position, 7,', '||column_name,NULL)) ||
max(decode(position, 8,', '||column_name,NULL)) ||
max(decode(position, 9,', '||column_name,NULL)) ||
max(decode(position,10,', '||column_name,NULL)) ||
max(decode(position,11,', '||column_name,NULL)) ||
max(decode(position,12,', '||column_name,NULL)) ||
max(decode(position,13,', '||column_name,NULL)) ||
max(decode(position,14,', '||column_name,NULL)) ||
max(decode(position,15,', '||column_name,NULL)) ||
max(decode(position,16,', '||column_name,NULL)) columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name ) a,
( select table_name, index_name,
max(decode(column_position, 1, column_name,NULL)) ||
max(decode(column_position, 2,', '||column_name,NULL)) ||
max(decode(column_position, 3,', '||column_name,NULL)) ||
max(decode(column_position, 4,', '||column_name,NULL)) ||
max(decode(column_position, 5,', '||column_name,NULL)) ||
max(decode(column_position, 6,', '||column_name,NULL)) ||
max(decode(column_position, 7,', '||column_name,NULL)) ||
max(decode(column_position, 8,', '||column_name,NULL)) ||
max(decode(column_position, 9,', '||column_name,NULL)) ||
max(decode(column_position,10,', '||column_name,NULL)) ||
max(decode(column_position,11,', '||column_name,NULL)) ||
max(decode(column_position,12,', '||column_name,NULL)) ||
max(decode(column_position,13,', '||column_name,NULL)) ||
max(decode(column_position,14,', '||column_name,NULL)) ||
max(decode(column_position,15,', '||column_name,NULL)) ||
max(decode(column_position,16,', '||column_name,NULL)) columns
from user_ind_columns
group by table_name, index_name ) b
where a.table_name = b.table_name (+)
and b.columns (+) like a.columns || '%'


 
 
select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') ||
 ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE test ;' Indx from
 (select cc.owner, cc.TABLE_NAME, cc.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)) col_list
 from dba_constraints dc, dba_cons_columns cc
 where dc.owner = cc.owner
 and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
 and dc.CONSTRAINT_type = 'R'
 and upper(dc.owner) = upper('CAR')
 group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME
 ) con
 where not exists (
 select 1 from
 ( select table_owner, table_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)) col_list
 from dba_ind_columns
 where upper(table_owner) = upper('CAR')
 group by table_owner, table_name, index_name ) col
 where con.owner = col.table_owner
 and con.table_name = col.table_name 
 and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ; 
 
set verify on
set pages 60
 
=============

Example !!!!!!!!!

CREATE TABLE scott.test11 (col1 NUMBER NOT NULL, col2 NUMBER);

ALTER TABLE scott.test11 ADD CONSTRAINT test11_UNIQUE UNIQUE (col1, col2);



session1> INSERT INTO scott.test11 VALUES (11, NULL);
session2> INSERT INTO scott.test11 VALUES (22, NULL);

session2> INSERT INTO scott.test11 VALUES (11, NULL); -- <= this session will hang  !!!!!!!


col INDX for a80
select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') || ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE test ;' Indx from
 (select cc.owner, cc.TABLE_NAME, cc.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)) col_list
 from dba_constraints dc, dba_cons_columns cc
 where dc.owner = cc.owner
 and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
 and dc.CONSTRAINT_type = 'R'
 and upper(dc.owner) = upper('SCOTT')
 group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME
 ) con
 where not exists (
 select 1 from
 ( select table_owner, table_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)) col_list
 from dba_ind_columns
 where 1=1 
 and upper(table_owner) = upper('SCOTT')
 group by table_owner, table_name, index_name ) col
 where con.owner = col.table_owner
 and con.table_name = col.table_name 
 and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ; 
 set verify on pages 60
 



after creating index you will create following message !!


SQL> CREATE INDEX SCOTT.IX_DEPTNO ON SCOTT.EMP ("DEPTNO");

Index created.



SQL> INSERT INTO scott.test11 VALUES (11, NULL);
INSERT INTO scott.test11 VALUES (11, NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST11_UNIQUE) violated   <<<<<<

==============================================================

set linesize 300 pagesize 300
col INDX for a200
select 'CREATE INDEX ' || owner || '.' || replace(CONSTRAINT_NAME,'FK_','IX_') || ' ON ' || owner || '.' || table_name || ' (' || col_list ||') TABLESPACE XXXX ;' Indx from
(select cc.owner, cc.TABLE_NAME, cc.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)) col_list from dba_constraints dc, dba_cons_columns cc
where dc.owner = cc.owner
and dc.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
and dc.CONSTRAINT_type = 'R'
and upper(dc.owner) not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','PERFSTAT','ORDDATA','BDA_METADATA')
group by cc.owner, cc.TABLE_NAME, cc.CONSTRAINT_NAME
) con
where not exists (
select 1 from
( select table_owner, table_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)) col_list from dba_ind_columns
where 1=1
and upper(table_owner) not in ( 'SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC','WWV_FLOW_PLATFORM','PERFSTAT','ORDDATA','BDA_METADATA')
group by table_owner, table_name, index_name ) col
where con.owner = col.table_owner
and con.table_name = col.table_name
and con.col_list = substr(col.col_list, 1, length(con.col_list) ) ) ;

===============================

Script to Check for Foreign Key Locking Issues for a Specific User ( Doc ID 1019527.6 )

run as sys/system

drop table ck_log;

create table ck_log (LineNum number,LineMsg varchar2(2000)); <<<< Create this table first

save this script into fkidx.sql

declare

t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type;

t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type;

t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;

t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;

tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type;

tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type;

tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;

tt_POSITION USER_CONS_COLUMNS.POSITION%type;

tt_Dummy number;

tt_dummyChar varchar2(2000);

l_Cons_Found_Flag VarChar2(1);

Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;

Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;

Err_POSITION USER_CONS_COLUMNS.POSITION%type;

l_OWNER dba_tables.OWNER%type := 'ANUJ'; ---- change username

tLineNum number;

cursor UserTabs is select table_name from dba_tables

where 1=1

and OWNER=l_OWNER

order by table_name;

cursor TableCons is

select CONSTRAINT_TYPE,CONSTRAINT_NAME,R_CONSTRAINT_NAME from dba_constraints

where OWNER = l_OWNER

and table_name = t_Table_Name

and CONSTRAINT_TYPE = 'R'

order by TABLE_NAME, CONSTRAINT_NAME;

cursor ConColumns is select CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,POSITION from dba_cons_columns

where OWNER = l_OWNER

and CONSTRAINT_NAME = t_CONSTRAINT_NAME

order by POSITION;

cursor IndexColumns is

select TABLE_NAME,COLUMN_NAME,POSITION from dba_cons_columns

where OWNER = l_OWNER

and CONSTRAINT_NAME = t_CONSTRAINT_NAME

order by POSITION;

DebugLevel number := 99; -- >>> 99 = dump all info`

DebugFlag varchar(1) := 'N'; -- Turn Debugging on

t_Error_Found varchar(1);

begin

tLineNum := 1000;

open UserTabs;

LOOP

Fetch UserTabs into t_TABLE_NAME;

t_Error_Found := 'N';

exit when UserTabs%NOTFOUND;

-- Log current table

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL );

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Checking Table '||t_Table_Name);

l_Cons_Found_Flag := 'N';

open TableCons;

LOOP

FETCH TableCons INTO t_CONSTRAINT_TYPE,t_CONSTRAINT_NAME,t_R_CONSTRAINT_NAME;

exit when TableCons%NOTFOUND;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);

commit;

end;

end if;

open ConColumns;

LOOP

FETCH ConColumns INTO tt_CONSTRAINT_NAME,tt_TABLE_NAME,tt_COLUMN_NAME,tt_POSITION;

exit when ConColumns%NOTFOUND;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, NULL );

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME);

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME);

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Found POSITION = '|| tt_POSITION);

commit;

end;

end if;

begin

select 1 into tt_Dummy from dba_ind_columns

where TABLE_NAME = tt_TABLE_NAME

and COLUMN_NAME = tt_COLUMN_NAME

and COLUMN_POSITION = tt_POSITION;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' );

end;

end if;

exception

when Too_Many_Rows then

if ( DebugFlag = 'Y' and DebugLevel >= 99 )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Row Has matching Index' );

end;

end if;

when no_data_found then

if ( DebugFlag = 'Y' and DebugLevel >= 99 )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'NO MATCH FOUND' );

commit;

end;

end if;

t_Error_Found := 'Y';

select distinct TABLE_NAME into tt_dummyChar from dba_cons_columns

where OWNER =l_OWNER

and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum, 'Changing data in table '||tt_dummyChar||' will lock table ' ||tt_TABLE_NAME);

commit;

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Create an index on table '||tt_TABLE_NAME ||' with the following columns to remove lock problem');

open IndexColumns ;

loop

Fetch IndexColumns into Err_TABLE_NAME,Err_COLUMN_NAME,Err_POSITION;

exit when IndexColumns%NotFound;

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');

end loop;

close IndexColumns;

end;

end loop;

commit;

close ConColumns;

end loop;

if ( t_Error_Found = 'N' )

then

begin

tLineNum := tLineNum + 1;

insert into ck_log ( LineNum, LineMsg ) values ( tLineNum,'No foreign key errors found');

end;

end if;

commit;

close TableCons;

end loop;

commit;

end;

sqlplus> @fkidx.sql

for report !!!

set pagesize 600 linesize 300

select LineMsg from ck_log

where LineMsg NOT LIKE 'Checking%' AND LineMsg NOT LIKE 'No foreign key%'

order by LineNum

===================

SET HEADING ON

SET ECHO ON

SET FEEDBACK 6

SET LINESIZE 80

SET PAGESIZE 50

SELECT

-- Generate the CREATE INDEX command

'CREATE INDEX ' ||

-- Create a descriptive index name (e.g., IX_EMP_FK_DEPT_ID)

'IXF_' || SUBSTR(acc.table_name, 1, 15) || '_' || SUBSTR(acc.constraint_name, 1, 10) ||

' ON ' ||

acc.owner || '.' || acc.table_name ||

' (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ');' AS create_index_command

FROM

all_cons_columns acc

JOIN

all_constraints ac ON acc.owner = ac.owner AND acc.constraint_name = ac.constraint_name

LEFT JOIN

all_ind_columns aic ON acc.owner = aic.table_owner

AND acc.table_name = aic.table_name

AND acc.column_name = aic.column_name

AND acc.position = aic.column_position

WHERE

ac.owner = 'XXX' -- <-- *** REPLACE THIS WITH YOUR SCHEMA NAME (IN CAPITALS) ***

AND ac.constraint_type = 'R' -- Only include Foreign Keys

-- Exclude Foreign Keys that already have a supporting index

AND NOT EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = acc.owner

AND aic_check.table_name = acc.table_name

-- Check if the index covers at least the first column of the FK

AND aic_check.column_name = acc.column_name

AND acc.position = 1 -- Only need to check for the first column match

)

GROUP BY

acc.owner, acc.table_name, acc.constraint_name

ORDER BY

acc.table_name, acc.constraint_name;

col CREATE_INDEX_COMMAND for a120

SELECT

-- Generate the CREATE INDEX command

'CREATE INDEX ' ||

-- Create a descriptive index name (e.g., IXF_EMPLOYEES_FK_DEPT)

'IXF_' || SUBSTR(acc.table_name, 1, 15) || '_' || SUBSTR(acc.constraint_name, 1, 10) ||

' ON ' ||

acc.owner || '.' || acc.table_name ||

' (' || LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position) || ')' ||

-- *** ADD TABLESPACE CLAUSE ***

' XXXX_INDX ;' AS create_index_command

FROM

all_cons_columns acc

JOIN

all_constraints ac ON acc.owner = ac.owner AND acc.constraint_name = ac.constraint_name

LEFT JOIN

all_ind_columns aic ON acc.owner = aic.table_owner

AND acc.table_name = aic.table_name

AND acc.column_name = aic.column_name

AND acc.position = aic.column_position

WHERE

ac.owner = 'XXXXX' --- <-- *** REPLACE THIS WITH YOUR SCHEMA NAME (IN CAPITALS) ***

AND ac.constraint_type = 'R' -- Only include Foreign Keys

-- Exclude Foreign Keys that already have a supporting index

AND NOT EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = acc.owner

AND aic_check.table_name = acc.table_name

-- Check if the index covers at least the first column of the FK

AND aic_check.column_name = acc.column_name

AND acc.position = 1

)

GROUP BY

acc.owner, acc.table_name, acc.constraint_name

ORDER BY

acc.table_name, acc.constraint_name;

========================================

DECLARE

-- === 1. USER CONFIGURATION SECTION ===

v_schema_name CONSTANT VARCHAR2(128) := 'SCHEMA_NAME_HERE'; -- <-- SET YOUR SCHEMA NAME (UPPERCASE)

v_tablespace_name CONSTANT VARCHAR2(128) := 'INDEX_TABLESPACE_NAME'; -- <-- SET THE TARGET TABLESPACE

-- =====================================

v_create_index_sql VARCHAR2(4000);

v_index_name VARCHAR2(128);

v_column_list VARCHAR2(4000);

v_missing_index BOOLEAN;

-- Cursor to find all Foreign Key constraints (R) in the target schema

CURSOR c_fk_constraints IS

SELECT

ac.table_name,

ac.constraint_name

FROM

all_constraints ac

WHERE

ac.owner = v_schema_name

AND ac.constraint_type = 'R';

BEGIN

DBMS_OUTPUT.PUT_LINE('--- Starting FK Index Check and Creation Script for Schema: ' || v_schema_name || ' ---');

DBMS_OUTPUT.PUT_LINE('New indexes will be placed in TABLESPACE: ' || v_tablespace_name);

FOR fk_rec IN c_fk_constraints LOOP

-- 1. Get the comma-separated list of columns for the current FK

SELECT

LISTAGG(acc.column_name, ', ') WITHIN GROUP (ORDER BY acc.position),

-- Check if a matching index exists for the first column

CASE WHEN EXISTS (

SELECT 1

FROM all_ind_columns aic_check

WHERE aic_check.table_owner = v_schema_name

AND aic_check.table_name = fk_rec.table_name

AND aic_check.column_name = acc.column_name

AND acc.position = 1 -- Only need to match the first column

) THEN FALSE ELSE TRUE END

INTO

v_column_list,

v_missing_index

FROM

all_cons_columns acc

WHERE

acc.owner = v_schema_name

AND acc.constraint_name = fk_rec.constraint_name

GROUP BY acc.owner, acc.table_name, acc.constraint_name;

-- 2. Check if the index is missing

IF v_missing_index THEN

-- Generate a unique, descriptive index name (max 30 chars for older versions)

v_index_name := 'IXF_' || SUBSTR(fk_rec.table_name, 1, 15) || '_' || SUBSTR(fk_rec.constraint_name, 1, 10);

-- Construct the CREATE INDEX statement

v_create_index_sql :=

'CREATE INDEX ' || v_schema_name || '.' || v_index_name ||

' ON ' || v_schema_name || '.' || fk_rec.table_name ||

' (' || v_column_list || ')' ||

' TABLESPACE ' || v_tablespace_name;

-- 3. Execute the DDL

DBMS_OUTPUT.PUT_LINE('Creating index ' || v_index_name || ' on ' || fk_rec.table_name);

DBMS_OUTPUT.PUT_LINE('SQL: ' || v_create_index_sql || ';');

EXECUTE IMMEDIATE v_create_index_sql;

ELSE

DBMS_OUTPUT.PUT_LINE('Index exists for FK ' || fk_rec.constraint_name || ' on ' || fk_rec.table_name || '. Skipping.');

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE('--- Script Finished. ---');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('!!! ERROR occurred while processing: ' || fk_rec.table_name || ' / ' || fk_rec.constraint_name || '!!!');

DBMS_OUTPUT.PUT_LINE('SQL Error: ' || SQLERRM);

-- Re-raise the exception to halt the script and signal failure

RAISE;

END;

====

https://github.com/fatdba/Oracle-Database-Scripts/blob/main/fklocking.sql

set serveroutput on
	declare
	procedure print_all(s varchar2) is begin null;
	dbms_output.put_line(s);
	end;
	procedure print_ddl(s varchar2) is begin null;
	dbms_output.put_line(s);
	end;
	begin
	dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
	for a in (
	select count(*) samples,
	event,p1,p2,o.owner c_owner,o.object_name c_object_name,p.object_owner p_owner,p.object_name p_object_name,id,operation,min(p1-1414332420+4) lock_mode,min(sample_time) min_time,max(sample_time) max_time,ceil(10*count(distinct sample_id)/60) minutes
	from dba_hist_active_sess_history left outer join dba_hist_sql_plan p using(dbid,sql_id) left outer join dba_objects o on object_id=p2 left outer join dba_objects po on po.object_id=current_obj#
	where event like 'enq: TM%' and p1>=1414332420 and sample_time>sysdate-15 and p.id=1 and operation in('DELETE','UPDATE','MERGE')
	group by
	event,p1,p2,o.owner,o.object_name,p.object_owner,p.object_name,po.owner,po.object_name,id,operation
	order by count(*) desc
	) loop
	print_ddl('-- '\|\|a.operation\|\|' on '\|\|a.p_owner\|\|'.'\|\|a.p_object_name\|\|' has locked '\|\|a.c_owner\|\|'.'\|\|a.c_object_name\|\|' in mode '\|\|a.lock_mode\|\|' for '\|\|a.minutes\|\|' minutes between '\|\|to_char(a.min_time,'dd-mon hh24:mi')\|\|' and '\|\|to_char(a.max_time,'dd-mon hh24:mi'));
	for s in (
	select distinct regexp_replace(cast(substr(sql_text,1,2000) as varchar2(60)),'[^a-zA-Z ]',' ') sql_text
	from dba_hist_active_sess_history join dba_hist_sqltext t using(dbid,sql_id)
	where event like 'enq: TM%' and p2=a.p2 and sample_time>sysdate-90
	) loop
	print_all('-- '\|\|'blocked statement: '\|\|s.sql_text);
	end loop;
	for c in (
	with
	c as (
	select p.owner p_owner,p.table_name p_table_name,c.owner c_owner,c.table_name c_table_name,c.delete_rule,c.constraint_name
	from dba_constraints p
	join dba_constraints c on (c.r_owner=p.owner and c.r_constraint_name=p.constraint_name)
	where p.constraint_type in ('P','U') and c.constraint_type='R'
	)
	select c_owner owner,constraint_name,c_table_name,connect_by_root(p_owner\|\|'.'\|\|p_table_name)\|\|sys_connect_by_path(decode(delete_rule,'CASCADE','(cascade delete)','SET NULL','(cascade set null)',' ')\|\|' '\|\|c_owner\|\|'"."'\|\|c_table_name,' referenced by') foreign_keys
	from c
	where level<=10 and c_owner=a.c_owner and c_table_name=a.c_object_name
	connect by nocycle p_owner=prior c_owner and p_table_name=prior c_table_name and ( level=1 or prior delete_rule in ('CASCADE','SET NULL') )
	start with p_owner=a.p_owner and p_table_name=a.p_object_name
	) loop
	print_all('-- '\|\|'FK chain: '\|\|c.foreign_keys\|\|' ('\|\|c.owner\|\|'.'\|\|c.constraint_name\|\|')'\|\|' unindexed');
	for l in (select * from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name) loop
	print_all('-- FK column '\|\|l.column_name);
	end loop;
	print_ddl('-- Suggested index: '\|\|regexp_replace(translate(dbms_metadata.get_ddl('REF_CONSTRAINT',c.constraint_name,c.owner),chr(10)\|\|chr(13),' '),'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].[)]). REFERENCES ".*','CREATE INDEX ON \1 \3;'));
	for x in (
	select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)\|\|chr(13),' ')) ddl
	from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
	and column_name in (select column_name from dba_cons_columns where owner=c.owner and constraint_name=c.constraint_name)
	)
	loop
	print_ddl('-- Existing candidate indexes '\|\|x.ddl);
	end loop;
	for x in (
	select rtrim(translate(dbms_metadata.get_ddl('INDEX',index_name,index_owner),chr(10)\|\|chr(13),' ')) ddl
	from dba_ind_columns where (index_owner,index_name) in (select owner,index_name from dba_indexes where owner=c.owner and table_name=c.c_table_name)
	)
	loop
	print_all('-- Other existing Indexes: '\|\|x.ddl);
	end loop;
	end loop;
	end loop;
	end;
	/

==================

https://github.com/fatdba/Oracle-Database-Scripts/blob/main/Nonindexedfkconstraints.sql

set linesize 500 pagesize 300

col CONSTRAINT_NAME for a20

col R_CONSTRAINT_NAME for a20

col OWNER for a14

col PARENT_OWNER for a14

col PARENT_CONSTRAINT_NAME for a14

col TABLE_NAME for a26

col PARENT_TABLE_NAME for a15

col R_OWNER for a14

col col_01 for a25

col col_02 for a15

col col_03 for a15

col col_04 for a15

col col_05 for a15

col col_06 for a15

col col_07 for a15

col col_08 for a15

col col_09 for a15

col col_10 for a15

col col_11 for a15

col col_12 for a15

col col_13 for a15

col col_14 for a15

col col_15 for a15

col col_16 for a15

WITH

ref_int_constraints AS (

SELECT

col.owner,

col.table_name,

col.constraint_name,

con.status,

con.r_owner,

con.r_constraint_name,

COUNT(*) col_cnt,

MAX(CASE col.position WHEN 01 THEN col.column_name END) col_01,

MAX(CASE col.position WHEN 02 THEN col.column_name END) col_02,

MAX(CASE col.position WHEN 03 THEN col.column_name END) col_03,

MAX(CASE col.position WHEN 04 THEN col.column_name END) col_04,

MAX(CASE col.position WHEN 05 THEN col.column_name END) col_05,

MAX(CASE col.position WHEN 06 THEN col.column_name END) col_06,

MAX(CASE col.position WHEN 07 THEN col.column_name END) col_07,

MAX(CASE col.position WHEN 08 THEN col.column_name END) col_08,

MAX(CASE col.position WHEN 09 THEN col.column_name END) col_09,

MAX(CASE col.position WHEN 10 THEN col.column_name END) col_10,

MAX(CASE col.position WHEN 11 THEN col.column_name END) col_11,

MAX(CASE col.position WHEN 12 THEN col.column_name END) col_12,

MAX(CASE col.position WHEN 13 THEN col.column_name END) col_13,

MAX(CASE col.position WHEN 14 THEN col.column_name END) col_14,

MAX(CASE col.position WHEN 15 THEN col.column_name END) col_15,

MAX(CASE col.position WHEN 16 THEN col.column_name END) col_16,

par.owner parent_owner,

par.table_name parent_table_name,

par.constraint_name parent_constraint_name

FROM dba_constraints con,

dba_cons_columns col,

dba_constraints par

WHERE 1=1

and con.constraint_type = 'R'

-- AND con.owner NOT IN ('ANONYMOUS','APEX_030200','APEX_040000','APEX_SSO','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','MDSYS','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS')

--AND con.owner NOT IN ('SI_INFORMTN_SCHEMA','SQLTXADMIN','SQLTXPLAIN','SYS','SYSMAN','SYSTEM','TRCANLZR','WMSYS','XDB','XS$NULL','PERFSTAT','STDBYPERF','MGDSYS','OJVMSYS')

and con.owner in ('ANUJ') ---- <<<<

AND col.owner = con.owner

AND col.constraint_name = con.constraint_name

AND col.table_name = con.table_name

AND par.owner(+) = con.r_owner

AND par.constraint_name(+) = con.r_constraint_name

GROUP BY

col.owner,

col.constraint_name,

col.table_name,

con.status,

con.r_owner,

con.r_constraint_name,

par.owner,

par.constraint_name,

par.table_name

ref_int_indexes AS (

SELECT /*+ MATERIALIZE NO_MERGE */ /* 2a.87 */

r.owner,

r.constraint_name,

c.table_owner,

c.table_name,

c.index_owner,

c.index_name,

r.col_cnt

FROM ref_int_constraints r,

dba_ind_columns c,

dba_indexes i

WHERE c.table_owner = r.owner

AND c.table_name = r.table_name

AND c.column_position <= r.col_cnt

AND c.column_name IN (r.col_01, r.col_02, r.col_03, r.col_04, r.col_05, r.col_06, r.col_07, r.col_08,

r.col_09, r.col_10, r.col_11, r.col_12, r.col_13, r.col_14, r.col_15, r.col_16)

AND i.owner = c.index_owner

AND i.index_name = c.index_name

AND i.table_owner = c.table_owner

AND i.table_name = c.table_name

AND i.index_type != 'BITMAP'

GROUP BY

r.owner,

r.constraint_name,

c.table_owner,

c.table_name,

c.index_owner,

c.index_name,

r.col_cnt

HAVING COUNT(*) = r.col_cnt

)

SELECT /*+ NO_MERGE */ /* 2a.87 */

FROM ref_int_constraints c

WHERE NOT EXISTS (

SELECT NULL

FROM ref_int_indexes i

WHERE i.owner = c.owner

AND i.constraint_name = c.constraint_name

)

ORDER BY

1, 2, 3;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews

Tuesday, 6 September 2011

How does one enable the SQL*Plus HELP facility?

Oracle Table size

Monday, 5 September 2011

Oracle Count alphabet

Saturday, 3 September 2011

encrypted data in Oracle

Oracle foreign key constraints procedure

Oracle Box ip address on SQLPLUS

Oracle missing index on foreign key

Oracle : Foreign Keys without Index

Oracle DBA

anuj blog Archive