Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: May 04, 2017 - 2:59 am UTC

Last updated: May 04, 2017 - 12:10 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hi Connor/Chris,

We have two databases namely primary and secondary.

In primary we have around 1000 tables out of which we need to create 40 tables on secondary with exact same columns names and data types along with indexes.

Can you suggest any option or script using which we can get the table deatils from all_tab_columns from primary and apply it on secondary?

and Connor said...

Lots of options:

1) create table T as select * from T@src

if you just want the data

2) DataPump export/import

Use the INCLUDE filter to nominate the tables you need

3) DBMS_METADATA.GET_DDL

gets the exact ddl for each object so you can re-run that on the target node

Option 2 probably would be easiest

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Copy constraints & indexes

A reader, May 04, 2017 - 3:57 am UTC

Thanks Connor!!

With both the options tables gets rated but for constraints and indexes I need to manually look for 40 tables and create each table Index, constraint.


Connor McDonald
May 04, 2017 - 12:10 pm UTC

By default, dbms_metadata will include all the constraints, for example:

SQL> select DBMS_METADATA.GET_DDL('TABLE','CUSTOMERS',user) from dual;

DBMS_METADATA.GET_DDL('TABLE','CUSTOMERS',USER)
-------------------------------------------------------------------
  CREATE TABLE "MCDONAC"."CUSTOMERS"
   (    "CUST_ID" NUMBER(*,0),
        "NAME" VARCHAR2(100),
        "SIGNUP" DATE,
        "CREDITLIMIT" NUMBER(*,0),
        "VIP" VARCHAR2(1),
        "STORE_ID" NUMBER(*,0) NOT NULL ENABLE,
         PRIMARY KEY ("CUST_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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "CUST_FK" FOREIGN KEY ("STORE_ID")
          REFERENCES "MCDONAC"."STORES" ("STORE_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


or you can use DataPump and then run impdp to generate sqlfile
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.