From oracle-l-bounce@freelists.org Mon Mar 1 12:43:11 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i21IhBF13814 for ; Mon, 1 Mar 2004 12:43:11 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i21IhAo13807 for ; Mon, 1 Mar 2004 12:43:10 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B46CA394DB2; Mon, 1 Mar 2004 13:42:33 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 01 Mar 2004 13:41:25 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mail.abwf.com (mail.abwf.com [65.114.91.24]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id F0348394F40 for ; Mon, 1 Mar 2004 13:41:20 -0500 (EST) Received: from mail01.abwf.com [172.16.18.12] by mail.abwf.com [172.16.19.47] (CMSPraetor 5.10.4411) with ESMTP id 110F4D0BF6F846489FFADBE94769170C for ; Mon, 01 Mar 2004 13:40:27 -0500 Received: by mail01.corp.abwf.com with Internet Mail Service (5.5.2653.19) id ; Mon, 1 Mar 2004 13:40:26 -0500 From: Muqthar Ahmed To: "'oracle-l@freelists.org'" Message-ID: <6362087FB073D41195B800D0B78284BE0621C83A@mail01.corp.abwf.com> Subject: RE: Function - Based Indexes Date: Mon, 1 Mar 2004 13:40:15 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain X-archive-position: 137 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Muqthar.Ahmed@decoratetoday.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Rafiq, May be I did not explain properly. If I use the following query, I will get index names and column names: select index_name, column_name, column_position from all_ind_columns where table_name = upper('&tab') and index_owner = upper('&owner') order by index_name, column_position INDEX_NAME COLUMN_NAME COLUMN_POSITION ------------------------------ ------------------------- --------------- TBLORD_IDX1 OOOID 1 TBLORD_IDX1 SYS_NC00081$ 2 TBLORD_IDX1 OOODERNO 3 TBLORD_IDX2 OOOID 1 TBLORD_IDX2 OOOTOTAL 2 TBLORD_IDX2 OOODATE 3 TBLORD_IDX3 OOOID 1 TBLORD_IDX3 OOOUBMITDATE 2 TBLORD_IDX4 SYS_NC00064$ 1 TBLORD_IDX5 SYS_NC00066$ 1 But it will not display function-based information. In the above example, it is showing only: SYS_NC00081$ SYS_NC00064$ SYS_NC00066$ Thanks Muqthar -----Original Message----- From: M Rafiq [mailto:rafiq9857@hotmail.com] Sent: Monday, March 01, 2004 1:28 PM To: oracle-l@freelists.org Subject: RE: Function - Based Indexes Run this script SELECT index_name, table_name, status FROM dba_indexes WHERE FUNCIDX_STATUS is not null / Regards Rafiq From: Muqthar Ahmed Reply-To: oracle-l@freelists.org To: "'oracle-l@freelists.org'" Subject: Function - Based Indexes Date: Mon, 1 Mar 2004 13:05:45 -0500 Hi, If an Index is created on a column using Function then how do you find out this information using Data Dictionary. For example: create index tab1_idx1 on tab1(trim(lower(billaddress))) tablespace users; Usually I export table without DATA to see Create Index syntax. Please let me know if I can use any Data Dictionary. Thanks Muqthar Ahmed * * * * * * * * * The information contained in this E-mail message is privileged, confidential, and may be protected from disclosure; please be aware that any other use, printing,copying, disclosure or dissemination of this communication may be subject to legal restriction or sanction. If you think that you have received this E-mail message in error, please reply to the sender and delete it from your computer. Thank you. _________________________________________________________________ Stay informed on Election 2004 and the race to Super Tuesday. http://special.msn.com/msn/election2004.armx ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- * * * * * * * * * The information contained in this E-mail message is privileged, confidential, and may be protected from disclosure; please be aware that any other use, printing,copying, disclosure or dissemination of this communication may be subject to legal restriction or sanction. If you think that you have received this E-mail message in error, please reply to the sender and delete it from your computer. Thank you. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------