Home » SQL & PL/SQL » SQL & PL/SQL » dynamically creating index from same table in different schema (11g release 2)
dynamically creating index from same table in different schema [message #661320] Tue, 14 March 2017 09:36 Go to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
Hi Team,

Here I am trying to create index for a table "src_table" in user "schema_owner_a" using the same table "src_table" which is already created in schema_owner_b from another schema user


please find the below sample of retrieving the column name and constraint name
as would like to know how to get the index

ALL_IND_COLUMNS,all_indexes

[code]
select
b.uniqueness, a.index_name, a.table_name, a.column_name
from all_ind_columns a, all_indexes b
[code/]

SELECT LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_id)
     INTO columnnames
     FROM all_tab_columns
    WHERE TABLE_NAME = src_table
    AND OWNER = owner_name;

  SELECT column_name
     INTO pkcolumnnames
     FROM (  SELECT wm_concat (cols.column_name) OVER (ORDER BY POSITION)
                       AS column_name,
                    COUNT (*) OVER () cnt,
                    POSITION
               FROM all_constraints cons, all_cons_columns cols
              WHERE     cols.TABLE_NAME = src_table
                    AND cons.owner = owner_name
                    AND cons.constraint_type = 'P'
                    AND cons.constraint_name = cols.constraint_name
                    AND cons.owner = cols.owner
           ORDER BY cols.TABLE_NAME, cols.POSITION ASC)
    WHERE POSITION = cnt;

   SELECT constraint_name
     INTO constraintname
     FROM all_constraints
    WHERE TABLE_NAME = src_table 
    AND OWNER = owner_name 
    AND constraint_type = 'P';
Re: dynamically creating index from same table in different schema [message #661321 is a reply to message #661320] Tue, 14 March 2017 09:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Here I am trying to create index for a table "src_table" in user "schema_owner_a" using the same table "src_table"
>which is already created in schema_owner_b from another schema user

Why do you want INDEX owned by different schema?
What problem does this solve?
Re: dynamically creating index from same table in different schema [message #661323 is a reply to message #661321] Tue, 14 March 2017 09:50 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
I agree, it solves nothing. The index is only used during access of the table and if the problem is that you want to store it in another tablespace then create it in that tablespace after granting rights to the tablespace to schema_owner
Re: dynamically creating index from same table in different schema [message #661325 is a reply to message #661323] Tue, 14 March 2017 10:03 Go to previous messageGo to next message
raj85844
Messages: 26
Registered: November 2016
Location: chennai
Junior Member
Yes the purpose of fetched the columns and constraint and I index from the table from schema b is to replicate the same table in Schema a this is done on top of CTAS hence finding some help here

Here first I need to ignore the index which is created using primary key and not null then I want to taken the column name and index name and create it dynamically

Help me out in this

Cheers
Re: dynamically creating index from same table in different schema [message #661326 is a reply to message #661325] Tue, 14 March 2017 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
DBMS_METADATA.GET_DDL() would be better way to obtain desired DDL.
Re: dynamically creating index from same table in different schema [message #661330 is a reply to message #661326] Tue, 14 March 2017 12:14 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... or expdp/impdp with remap_schema option.

Previous Topic: How to find highest salary ?
Next Topic: calling a function as a input parameter for a stored procedure
Goto Forum:
  


Current Time: Thu Mar 28 15:17:10 CDT 2024