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

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||...............
----------------------------------------------------------------

or

select DBMS_METADATA.GET_DDL('INDEX','<INDEX NAME>') from DUAL;

Select allOpen in new window

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.

THANKS JINESH BUT i was having problem using the export/import facility so i decided to try copying it from sql plus

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.

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.

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)

Lucinda, can u tell me how many tables are there in SCOTT to be exported to the HELEN schema?

there are four tables

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;
/

Select allOpen in new window

THANKS JINESH JUST ONE MORE QUESTION DOES THIS COPY THE CONSTRAINTS, INDEXES E.T.C

No, it doesn't, those would have to re-created as well. I'm working on that script ...

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

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

Select allOpen in new window

@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.

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;

Select allOpen in new window

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> 

Select allOpen in new window

SET LONG 10000 ---> That did it! Thx pal :)