• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2731
  • Last Modified:

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
0
lucinda77
Asked:
lucinda77
  • 9
  • 6
  • 4
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
Jinesh KamdarCommented:
SET LONG 10000 ---> That did it! Thx pal :)
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 9
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now