I want to create a table based on existing table where indexing of existing table is also copied.
I have a table named 'A' having index defined on few columns. I want to create a table 'B' same as 'A' and having the same indexing.
Using
Create table B select * from A;
here indexing of A is not copied to created table B
Correct. Create table as select doesn't copy the indexes! You need to copy them manually.
You could create a script to generate the definitions by querying user_ind_columns along the lines of:
create table t (
x int,
y int,
z int
);
create index i1 on t(x);
create index i2 on t(y, z);
create index i3 on t(z, x);
select 'create index ... on ... (' ||
listagg(column_name, ',') within group (order by column_position) ||
');' inds
from user_ind_columns
where table_name = 'T'
group by index_name;
INDS
create index ... on ... (X);
create index ... on ... (Y,Z);
create index ... on ... (Z,X);
Or, if you also want to copy grants, triggers, etc. you could look into using dbms_redefinition.copy_table_dependents:
http://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS67523 https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1#redef-including-dependents