Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    87

    Unanswered: How to Extract Index Definitions for a schema

    Hi,
    Which way should I follow to extract index definitions of a schema which may have all types of indexes.
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135
    Plz Try out this.

    SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE ='INDEX'

  3. #3
    Join Date
    Nov 2002
    Posts
    833
    the easiest way is as follows:

    exp the schema with rows=n

    imp schema indexfile=<yourindexfile> yourindexfile will then contain all indexdefinitions ...

    for more parameters RTFM A96652-01 on database utilities tahiti.oracle.com

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    Thank You!
    I need to try out this . I hope this takes care of Function based indexes and indexes on Partitioned tables as well.

  5. #5
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Attached is a script I use to build the actual ddl for each index ...
    I will write a sql statement to call this for each index in a schema... It will spool the output (1 file for each table)... Go to the bottom of the script and change the directory structure to match your environment ...

    put this script in a directory location and call it from there ...

    sql> spool c:\build_indexes.sql
    sql> select distinct '@c:\...\gen_indx1 ' ||table_name||' schemaowner
    sql> from user_indexes;
    sql> @c:\build_indexes

    I spool the above to a file and execute the file

    HTH
    Gregg

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Forgot the attachment ... sorry
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    My home brews scripts have failed to keep pace with the newer features that Oracle has added into the DB. Therefore now I only use DBMS_METADATA to extract the necessary SQL to (re)build DB objects.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    anacedent...
    Very true ... I have tried to keep all mine updated throughout the years, but now with 9i, DBMS_METADATA appears to work well ...

    Gregg

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    87
    how to use dbms_metadata??

  10. #10
    Join Date
    Mar 2004
    Posts
    20

    user name list ...

    hi

    i want to get all the user names list in the database. is there any single query to retrive this ..


    john

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >how to use dbms_metadata??
    http://tahiti.oracle.com
    contain ALL of Oracle documentation.
    DBMS_METADATA is an Oracle supplied package
    which is fully documented and explained at the site listed above.
    Now go & RTFM!
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Mar 2004
    Location
    Concord, MA
    Posts
    27
    Originally posted by anacedent
    >how to use dbms_metadata??
    http://tahiti.oracle.com
    contain ALL of Oracle documentation.
    DBMS_METADATA is an Oracle supplied package
    which is fully documented and explained at the site listed above.
    Now go & RTFM!
    thanks for the link and reference.... good documentation out there ...
    ~bagchi

  13. #13
    Join Date
    Jan 2004
    Location
    India
    Posts
    113

    Re: user name list ...

    Originally posted by John felix
    hi

    i want to get all the user names list in the database. is there any single query to retrive this ..


    john
    It seems u did not get any answer to ur question..

    select username from dba_users;

    It was quite easy, i am sure u could have done it urself.

    No problem
    Kaushik

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •