dynamically creating index from same table in different schema [message #661320] |
Tue, 14 March 2017 09:36 |
|
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';
|
|
|
|
|
|
|
|