RE: desc indexes howto

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 3 Jul 2013 09:11:16 -0400
Message-ID: <01ec01ce77ee$cdc69b30$6953d190$_at_rsiz.com>

Doh. If you want that to print out pretty, I forgot to copy in the sqlplus formatting lines:

set null ~
column owner format a12
column column_name format a30
column column_position format 90 hea CP
column blevel format 0 hea B
break on owner on table_name skip 1 on TT on index_name on IT on U on blevel

I find the column break stuff to be useful visually, and without the column formats you get a lot of line wraps and non-useful whitespace. I usually run my sqlplus sessions at linesize 140 pagesize 40 null ~ these days, but I think q_icol will fit a bit narrower than that. I hope the decodes and column name shorthands are obvious.

Also, Gus helpfully mentioned {ALL|USER|DBA}_CONS_COLUMNS in addition to DBA_CONSTRAINTS for the constraints themselves. I usually look at those one at a time, but I probably should cobble up something to pretty print the results.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Wednesday, July 03, 2013 8:50 AM
To: jose.soares_at_sferacarta.com; oracle-l_at_freelists.org Subject: RE: desc indexes howto

For describing indexes I prefer

select i.owner,

   decode(i.table_owner,i.owner,' ','*') X,    i.table_name,
   substr(i.table_type,1,2) TT,
   i.index_name,
   substr(i.index_type,1,2) IT,
   substr(i.uniqueness,1,1) U,
   i.blevel,

   ic.column_name,
   ic.column_position,
   ic.descend

from dba_indexes i,dba_ind_columns ic

where i.table_owner = '&table_owner'
  and i.table_name in (&table_list)
  and i.owner       = ic.index_owner
  and i.index_name  = ic.index_name
  and i.table_owner = ic.table_owner
  and i.table_name  = ic.table_name

order by i.owner,i.table_name,i.index_name,ic.column_position

unless I'm looking for something else specific. The &table_list bit of the where clause is easily dropped if you're looking at an entire schema, likewise &table_owner if all schemas. Upper case is up to the user to enter on purpose, as are the surrounding ' marks and internal commas in the table_list.

DBA_CONSTRAINTS has the information on constraints.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jose Soares
Sent: Wednesday, July 03, 2013 3:15 AM
To: oracle-l_at_freelists.org
Subject: desc indexes howto

Hi all,

How can I have indexes/constraints info?

I tried to delete a row from a table in my db and I got this message:

(IntegrityError) ORA-02292: integrity constraint (SFERA.SYS_C0057216) violated - child record found

I can't realize what SYS_C0057216 is and to what it is linked. I would like to know how to have information about it. Is there a way to do that?

thanks for any help.
j

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Wed Jul 03 2013 - 15:11:16 CEST