11 Replies Latest reply: Nov 25, 2012 10:31 AM by rp0428 RSS

    How to create exact copy of a table

    user503699
      Hello,

      I must admit my "google skills" have failed me and it is possible that I might be missing something obvious here but allow me to explain.
      If I have a table that is populated/accessed by a typical OLTP application such that over time the data in the table may become "fragmented".
      I know that is a very controversial term I used but that is not the question here. A simple way to describe the state of the table might be
      that the table has always been populated with lots of small INSERTs, modified with lots of small UPDATEs and data has been deleted in small DELETEs.
      This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
      Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
      1) its data
      2) its constraints/indexes etc.
      3) its storage parameters
      4) data stored in EXACTLY same manner as in original table.

      To avoid complications, the table in question is just a normal heap table without any partitioning involved. While CREATE TABLE AS SELECT (or CREATE
      TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.

      Can somebody please help?

      Thanks in advance.
        • 1. Re: How to create exact copy of a table
          Purvesh K
          user503699 wrote:
          Hello,

          Now my question is how do I create exact copy of this table and its indexes AS THEY EXIST AT THE MOMENT that includes
          1) its data
          2) its constraints/indexes etc.
          3) its storage parameters
          4) data stored in EXACTLY same manner as in original table.

          To avoid complications, the table in question is just a normal heap table without any partitioning involved. While CREATE TABLE AS SELECT (or CREATE
          TABLE followed by INSERT) will take care of points (1) to (3) above, it will not achieve point (4) above.

          Can somebody please help?

          Thanks in advance.
          I feel you should resort to DBMS_METADATA.GET_DDL to get exact features of your base table. Using CTAS, AFAIK, the implicit constraints are not copied and the requirements of storage parameters would also be included.
          You can then copy data using INSERT INTO TABLE SELECT FROM Base_Table. (Choosing APPEND hint if required)

          I could not understand what you meant by point 4. Would you like to elaborate on it?
          • 2. Re: How to create exact copy of a table
            phaeus
            Hello,
            also one option that you can use is the make a datapumpt export/import (best case over db link) on the same database. So can make a copy of your object under a new name (with the remap option).

            regards
            Peter
            • 3. Re: How to create exact copy of a table
              Osama_Mustafa
              Use Copy Command in sqlplus
              copy from hr/hr@youdb to scott/tiger@yourdb CREATE COUNTRIES using select * from countries; 
              http://docs.oracle.com/cd/B10500_01/server.920/a90842/apb.htm
              • 4. Re: How to create exact copy of a table
                JohnWatson
                I believe that what you want to do is preserve the distriburion of rows through the blocks of the segment. None of the solutions offered so far will do that, because this is impossible with SQL. SQL understand rows and tables, nothing more: it is not aware of the physical placement. The only way is with a technique such as database cloning, which operates at the file and block level.
                • 5. Re: How to create exact copy of a table
                  Jonathan Lewis
                  JohnWatson wrote:
                  I believe that what you want to do is preserve the distriburion of rows through the blocks of the segment. None of the solutions offered so far will do that, because this is impossible with SQL. SQL understand rows and tables, nothing more: it is not aware of the physical placement. The only way is with a technique such as database cloning, which operates at the file and block level.
                  Or, as a variation on the same sort of theme, look at transportable tablespaces. If you want a copy of this table in another database then make the tablespace (and any associated tablespaces) readonly export it (them), copy the files to the second location (after which you can make the tablespaces read/write), then import the tablespace(s) on the second database, finally make the imported tablespace(s) read/write and drop the tables you don't want.

                  You could, in theory, copy the tablespaces back to the same database - but that would require you to mess about with renaming tablespaces part way through and changing owner names on objects.

                  Regards
                  Jonathan Lewis
                  • 6. Re: How to create exact copy of a table
                    Girish Sharma
                    Exp/Imp...
                    C:\Windows\system32>expdp scott/tiger dumpfile=scottemp.dmp directory=DATA_PUMP_DIR tables="emp" constraints=y indexes=y statistics=y
                    
                    Export: Release 11.2.0.1.0 - Production on Sat Nov 24 15:35:34 2012
                    
                    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                    
                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Legacy Mode Active due to the following parameters:
                    Legacy Mode Parameter: "constraints=TRUE" Location: Command Line, ignored.
                    Legacy Mode Parameter: "indexes=TRUE" Location: Command Line, ignored.
                    Legacy Mode Parameter: "statistics=y" Location: Command Line, ignored.
                    Legacy Mode has set reuse_dumpfiles=true parameter.
                    Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** dumpfile=scottemp.dmp directory=DATA_PUMP_DIR tables=emp reuse_dumpfiles=true
                    Estimate in progress using BLOCKS method...
                    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                    Total estimation using BLOCKS method: 64 KB
                    Processing object type TABLE_EXPORT/TABLE/TABLE
                    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                    . . exported "SCOTT"."EMP"                               8.570 KB      14 rows
                    Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
                    ******************************************************************************
                    Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
                      E:\APP\SERVERROOM\ADMIN\ORCL\DPDUMP\SCOTTEMP.DMP
                    Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 15:35:38
                    
                    C:\Windows\system32>impdp scott/tiger dumpfile=scottemp.dmp directory=DATA_PUMP_DIR tables="emp" constraints=y indexes=y statistics=y logfile=log.txt remap_schema=scott:hr
                    
                    Import: Release 11.2.0.1.0 - Production on Sat Nov 24 15:48:20 2012
                    
                    Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
                    
                    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    Legacy Mode Active due to the following parameters:
                    Legacy Mode Parameter: "constraints=TRUE" Location: Command Line, ignored.
                    Legacy Mode Parameter: "indexes=TRUE" Location: Command Line, ignored.
                    Legacy Mode Parameter: "statistics=y" Location: Command Line, ignored.
                    Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
                    Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** dumpfile=scottemp.dmp directory=DATA_PUMP_DIR tables=emp logfile=log.txt remap_schema=scott:hr
                    Processing object type TABLE_EXPORT/TABLE/TABLE
                    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
                    . . imported "HR"."EMP"                                  8.570 KB      14 rows
                    Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                    ORA-39083: Object type REF_CONSTRAINT failed to create with error:
                    ORA-00942: table or view does not exist
                    Failing sql is:
                    ALTER TABLE "HR"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "HR"."DEPT" ("DEPTNO") ENABLE NOVALIDATE
                    
                    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                    Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 15:48:22
                    
                    
                    C:\Windows\system32>sqlplus hr/hr
                    
                    SQL*Plus: Release 11.2.0.1.0 Production on Sat Nov 24 15:48:32 2012
                    
                    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
                    
                    
                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    SQL> select * from emp;
                    ...Result is there.
                    14 rows selected.
                    
                    SQL>
                    
                    ORA-39083: Object type REF_CONSTRAINT failed to create with error:
                    ORA-00942: table or view does not exist
                    are understandable.
                    Regards
                    Girish Sharma
                    • 7. Re: How to create exact copy of a table
                      Srini Chavali-Oracle
                      As noted above, exp/imp (or expdp/impdp) will not duplicate the physical data distribution on disk on the source that the OP desires.

                      HTH
                      Srini
                      • 8. Re: How to create exact copy of a table
                        user503699
                        Thank you everybody for your kind suggestions.
                        As John (& Jonathan) mentioned, I don't believe there is a straightforward way to create a copy of segment(s) (table and its indexes, in particular).
                        The question came up in my mind recently as a result of trying to tune some processes that work on a particular table. Due to the nature of the application,
                        this table tends to undergo a lot of INSERT/UPDATE/DELETEs (which also affects the indexes on this table). Now, I have managed to identify and resolve
                        many of these problems but during my investigation, I wanted to test out, many times, how my changes will work on an ACTUAL table and not a table
                        that I can create with test scripts. I believe that having this ability (to copy a segment) would have certainly helped the process of validating my proposed approaches.
                        Not sure if anybody else felt the need and hence this question.
                        Once again, thank you everybody for your help.
                        • 9. Re: How to create exact copy of a table
                          axlrose
                          create dest_table as select * from source_table
                          • 10. Re: How to create exact copy of a table
                            Srini Chavali-Oracle
                            gunsnroses_xjapan wrote:
                            create dest_table as select * from source_table
                            Pl re-read OP's requirements ;-)

                            Srini
                            • 11. Re: How to create exact copy of a table
                              rp0428
                              >
                              I wanted to test out, many times, how my changes will work on an ACTUAL table and not a table
                              that I can create with test scripts.
                              >
                              Although I follow what you are thinking I believe you may be overestimating how useful that approach will be. Those tests will not necessarily test how your changes will work on 'an ACTUAL table'; they will test how your changes will work on THAT actual table. Unless you firsts identify the actual parameters that are, or might be, contributing to the actual problem you are having with that table you can't be assured that any 'solution' you find will really address those parameters in general.

                              IMO the two main reasons the term 'fragmentation' is controversial is because, one, when someone uses that term, they do not sufficently define what they mean and two, they don't provide metrics that support their use of the term. Of course even if you do that people will disagree.

                              And there is at least one factor that, even if you document it, probably won't be agreed as to whether it indicates 'fragmentation'. Do you consider a table to be fragmented simply because sections of it are widely scattered within the tablespace or even among the multpile file of the tablespace? Some folks do and some don't. Odds are that if more than one object shares a tablespace then it is likely that the 'sections' of those objects will be interspersed between the 'section's of other objects in that tablespace.

                              You provided some indication that you are aware that there is some need to do this when you said
                              >
                              This has meant that the data is neither closely stored in data blocks nor is in any particular order (so there are some empty blocks under HWM).
                              >
                              Actual metrics might include
                              1. total number of rows
                              2. total number of blocks
                              3. number of totally empty blocks
                              4. avg number of records per block
                              5. the effective PCTFREE and PCTUSED values of the blocks

                              As others have hinted with their solutions there can be a large impact just due to the other objects that share the same tablespace. The impacts may very depending on how often those other objects are modified and how big those modifications typically are. One big load into another table can cause a large 'gap' in the consecutive blocks that your table might have otherwise used.

                              A block that appears to be fairly empty may simply be obeying the rules for the value of PCTFREE and PCTUSED that are in effect.
                              >
                              I believe that having this ability (to copy a segment) would have certainly helped the process of validating my proposed approaches.
                              >
                              That is a valid approach if you are trying to solve a problem with that particular table but, in general, it will only help for that particular table. If you don't know how that table got to that particular state then you also won't know when that solution is appropriate in the future.

                              A more general solution requires a more general test case. For a raw block-level data distribution issue at the table level you can create a test table that you fully populate with data to use the same number of below-the-HWM rows and then delete rows from blocks to get the same number of rows in each block that your original table has.

                              That can't reproduce the actual state of the indexes and also can't incorporate the effects that other objects sharing the tablespace can have.