2 Replies Latest reply: Jun 22, 2010 2:55 PM by Solomon Yakobson RSS

    create a copy of partition table with data

    653303
      I need to create a script which will select some partitioned tables from sys.all_tables based on some condition (such as PARTITIONED ='YES') and create a copy of the table with data.
      The copy should be replica of orginal table i.e it should also be partitioned.

      can you pls suggest the possible approches that I can followe to come up with an automated script i.e I run the script and copies of all the tables are created in same schema.

      Thanks
        • 1. Re: create a copy of partition table with data
          mseberg
          I think I would use Import/Export instead. A script could be massive and the maintenance brutal.

          Here's some SQL you could modify to get your tables

          SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, A.DATA_LENGTH,
          A.NULLABLE, A.COLUMN_ID AS POS, B.OWNER
          FROM SYS.DBA_TAB_COLUMNS A, SYS.DBA_TABLES B
          WHERE A.TABLE_NAME = B.TABLE_NAME
          AND B.PARTITIONED = 'YES'
          ORDER BY TABLE_NAME, COLUMN_ID;

          Once you have that create a parm file and add the tables to it.

          If you are looking to use this as a refresh consider writing a script which drops the target user and then recreates with GRANTS and then imports your data.

          Does this help?

          On the refresh you would DROP USER CASCADE

          You could probably write some PL/SQL that would create/recreate the parm file daily if needed.

          userid=system/password
          tables=
          file=dailypart.dmp
          log=dailypart.log
          STATISTICS=NONE
          GRANTS=Y

          Edited by: mseberg on Jun 22, 2010 2:33 PM
          • 2. Re: create a copy of partition table with data
            Solomon Yakobson
            Use DBMS_METADATA:
            set serveroutput on format word_wrapped
            declare
                v_def clob;
            begin
                for v_rec in (
                              select  owner,
                                      table_name,
                                      dbms_metadata.get_ddl('TABLE',table_name,owner) def
                                from  dba_tables
                                where owner = 'SCOTT'
                                  and partitioned = 'YES'
                                  and rownum = 1
                             ) loop
                  v_def := regexp_replace(
                                          v_rec.def,
                                          '^  CREATE TABLE "' || v_rec.owner || '"."' || v_rec.table_name || '"',
                                          '  CREATE TABLE "' || v_rec.owner || '"."NEW_' || v_rec.table_name || '"'
                                         );
                  dbms_output.put_line('============= Original CREATE TABLE ====================');
                  dbms_output.put_line(v_rec.def);
                  dbms_output.put_line('============= New CREATE TABLE ====================');
                  dbms_output.put_line(v_def);
                end loop;
            end;
            /
            ============= Original CREATE TABLE ====================
            
              CREATE TABLE "SCOTT"."SALES_LIST" 
               (    "SALESMAN_ID" NUMBER(5,0),
            
                    "SALESMAN_NAME" VARCHAR2(30), 
                    "SALES_STATE" VARCHAR2(20), 
                    "SALES_AMOUNT"
            NUMBER(10,0), 
                    "SALES_DATE" DATE
               ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
            255 
              STORAGE(
              BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" 
              PARTITION BY LIST
            ("SALES_STATE") 
             (PARTITION "SALES_WEST"  VALUES ('California', 'Hawaii') 
            
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT
            1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST
            GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS , 
             PARTITION
            "SALES_EAST"  VALUES ('New York', 'Virginia', 'Florida') 
              PCTFREE 10 PCTUSED
            40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
            MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
            DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS , 
             PARTITION "SALES_CENTRAL"  VALUES
            ('Texas', 'Illinois') 
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
            
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE
            "USERS" NOCOMPRESS , 
             PARTITION "SALES_OTHER"  VALUES (DEFAULT) 
              PCTFREE 10
            PCTUSED 40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT 1048576
            MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
            BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS ) 
            
            ============= New CREATE TABLE ====================
            
              CREATE TABLE "SCOTT"."NEW_SALES_LIST" 
               (    "SALESMAN_ID" NUMBER(5,0),
            
                    "SALESMAN_NAME" VARCHAR2(30), 
                    "SALES_STATE" VARCHAR2(20), 
                    "SALES_AMOUNT"
            NUMBER(10,0), 
                    "SALES_DATE" DATE
               ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
            255 
              STORAGE(
              BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" 
              PARTITION BY LIST
            ("SALES_STATE") 
             (PARTITION "SALES_WEST"  VALUES ('California', 'Hawaii') 
            
            PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT
            1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST
            GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS , 
             PARTITION
            "SALES_EAST"  VALUES ('New York', 'Virginia', 'Florida') 
              PCTFREE 10 PCTUSED
            40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
            MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
            DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS , 
             PARTITION "SALES_CENTRAL"  VALUES
            ('Texas', 'Illinois') 
              PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
            
            STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
            
            PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
              TABLESPACE
            "USERS" NOCOMPRESS , 
             PARTITION "SALES_OTHER"  VALUES (DEFAULT) 
              PCTFREE 10
            PCTUSED 40 INITRANS 1 MAXTRANS 255 
              STORAGE(INITIAL 65536 NEXT 1048576
            MINEXTENTS 1 MAXEXTENTS 2147483645
              PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
            BUFFER_POOL DEFAULT)
              TABLESPACE "USERS" NOCOMPRESS ) 
            
            
            PL/SQL procedure successfully completed.
            
            SQL> 
            SY.