how do i copy a TABLE, AND IT'S INDEXES

how do i  copy a TABLE, AND IT'S INDEXES i.e primary key foreign key from one user to another in the same database
in sql plus


i've tried :

create table emp
as select * from scott.emp;

//but it doesn't copy the indexes i.e jit ust copies the tables and it's data from SCOTT

----------------------------------------------------------------------------------------------------
Providing scott has granted select privilege to helen or public on emp this would work.

i've also done this by logging into scott and running:

grant select on emp to helen;
or
grant select on emp to public;

public gives select privilege to everyone.

-------------------------------------------------------------------------

i want to copy the indexes as well?? please help
LVL 1
lucinda77Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sonicefuCommented:
one way is to use exp/imp utility to perform this task

----------------------------------------------------------------
second way is to generate script as follows

select 'create index '||index_name||...............
----------------------------------------------------------------
0
sonicefuCommented:
or
select DBMS_METADATA.GET_DDL('INDEX','<INDEX NAME>') from DUAL;

Open in new window

0
Jinesh KamdarCommented:
CREATE TABLE ... AS SELECT ... does not copy the constraints and indexes, but only the base table contents. I guess u wud hv to use EXPORT / IMPORT utilities for doing that or re-create constraints and indexes manually.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lucinda77Author Commented:
THANKS JINESH BUT i was having problem using the export/import facility so i decided to try copying it from sql plus
0
Jinesh KamdarCommented:
If this is for just 1 table, then i would suggest u to re-create the indexes and constraints manually in the new schema. If there are many tables involved, then we can help you create a script for doing this with the use of dictionary views. Let us know which option suits u best and then we can take it from there.
0
Jinesh KamdarCommented:
Better still, if u have TOAD, open the Schema Browser, select the table in the left-pane and the Scrit tab in the right-pane. This will show u all the DDLs necessary to re-create the table in the new schema.
0
lucinda77Author Commented:
hi jinesh i want to copy all the tables and indexes in SCOTT user to another user how do i do this using the data dictionary views(no i havent got TOAD installed)
0
Jinesh KamdarCommented:
Lucinda, can u tell me how many tables are there in SCOTT to be exported to the HELEN schema?
0
lucinda77Author Commented:
there are four tables
0
Jinesh KamdarCommented:
Ok. This seems to be under control then. For copying the tables, execute this script in the HELEN schema.
BEGIN
FOR i in (SELECT table_name FROM all_tables WHERE owner = 'SCOTT') LOOP
    EXECUTE IMMEDIATE 'CREATE TABLE ' || i.table_name || ' AS SELECT * FROM scott.' || i.table_name;
END LOOP;
END;
/

Open in new window

0
lucinda77Author Commented:
THANKS JINESH JUST ONE MORE QUESTION DOES THIS COPY THE CONSTRAINTS, INDEXES E.T.C
0
Jinesh KamdarCommented:
No, it doesn't, those would have to re-created as well. I'm working on that script ...
0
sonicefuCommented:
hi jinesh !
what if he use
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
select dbms_metadata.get_ddl('INDEX','IDX_NAME') from dual;
................
..................
because he has only 4 tables

or

set heading off
set pagesize 200
set col 500
spool c:\ddl.sql
select dbms_metadata.get_ddl(object_type,object_name) from all_objects;
spool off
0
sonicefuCommented:

set heading off
set pagesize 200
set colsize 500
spool c:\ddl.sql
select dbms_metadata.get_ddl(object_type,object_name) from user_objects;
spool off

Open in new window

0
Jinesh KamdarCommented:
@sonicefu: I tried that in my SQL*Plus and it didn't show me any lines after the first 2 :( Since COLSIZE is not a valid parameter in SQL*Plus, i replaced it with LINESIZE, but still no luck.
0
sonicefuCommented:
i got output like this when execute query, as i wrote it in code snippet

CREATE TABLE "HR"."REGIONS"
   (      "REGION_ID" NUMBER CONSTRAINT "REGION_ID_NN" NOT NULL ENABLE,
      "REGION_NAME" VARCHAR2(25),
       CONSTRAINT "REG_ID_PK" PRIMARY KEY ("REGION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
-----
CREATE UNIQUE INDEX "HR"."REG_ID_PK" ON "HR"."REGIONS" ("REGION_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
select dbms_metadata.get_ddl(object_type,object_name) from user_objects;

Open in new window

0
Jinesh KamdarCommented:
Somehow, it just does not seem to work for me in SQL*Plus, though it does in TOAD !!

Lucinda, please try sonicefu's approach and see if it works for u or not.
SQL> SET LINESIZE 500
SQL> SET PAGESIZE 200
SQL> select dbms_metadata.get_ddl(object_type,object_name) from user_objects where object_name like 'JAK%';
DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME)
--------------------------------------------------------------------------------
 
  CREATE TABLE "MERRILL"."JAK"
   (    "COL" VARCHAR2(400)
   ) PCTFREE 10 PCTUSE
 
 
  CREATE TABLE "MERRILL"."JAK1"
   (    "COL1" VARCHAR2(1),
        "COL2" VARCHAR2(1),
 
 
  CREATE UNIQUE INDEX "MERRILL"."JAK1" ON "MERRILL"."JAK1" ("COL1", "COL2", "CO
 
 
  CREATE TABLE "MERRILL"."JAK10"
   (    "C" VARCHAR2(10),
        "D" CLOB
   ) PCTFRE
 
 
  CREATE INDEX "MERRILL"."JAK_IDX" ON "MERRILL"."JAK" ("COL")
  PCTFREE 10 INI
 
 
SQL> 

Open in new window

0
sonicefuCommented:

SQL> SET HEADING OFF
SQL> SET PAGESIZE 100
SQL> SET LONG 10000
 
SQL> spool c:\ddl.sql
 
SQL> select dbms_metadata.get_ddl(object_type,object_name) 
     from user_objects;
SQL> spool off
 
SQL>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
SET LONG 10000 ---> That did it! Thx pal :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.