DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2012

    Part Copy of Production data to Development Environment


    I have a Production database with the current size involving two principale schemas:

    SQL> SELECT SUM(bytes)/1024/1024/1025 "Size in GB" FROM dba_segments where owner in ('SCHEMA1','SCHEMA2');

    Size in GB

    Out of these the mentioned tables and their indexes are taking up following space (175.326 GB):

    SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
    2 and segment_name in ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12');

    Size in GB

    SQL> SELECT SUM(bytes)/1024/1024/1024 "Size in GB" FROM dba_segments where owner IN ('SCHEMA1','SCHEMA2')
    2 and segment_name in (
    3 select index_name
    4 from dba_indexes
    5 where owner IN ('SCHEMA1','SCHEMA2')
    6 and table_name in ('TAB1','TAB2','TAB3','TAB4','TAB5','TAB6','TAB7','TAB8','TAB9','TAB10','TAB11','TAB12'));

    Size in GB

    SQL> select (72.0917968+103.234375) from dual;


    Thus these 12 tables along with the indexes takes about 88% of the total space in the 2 schemas.

    All 12 tables mentioned are subpartitioned based on a partition and subpartition key.

    We have 7 development databases which needs to be refreshed on demand with the production database.

    Currently, the refresh is done using RMAN, but going forward as the production database size increases we are anticipating space problems on development databases.

    The Solution which I tried:

    The 12 tables are partitioned in such a way that the data in one partition is functionally independent of other partition.

    I created new tablespaces and moved partitions to these new tablespaces based on their business functionality.
    Thus a single table had some partitions on one tablespace and rest of the partitions on the other tablespace.

    The idea was to transport the necessary tablespces (and not the entire database) across production to development database.

    Later on my DBA told me that for a tablespace to be transportable across databases they should be "self-contained".

    And one of the violations for self contained tablespace according to oracle documentation is:

    "A partitioned table is partially contained in the set of tablespaces.
    The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables."

    Thus my design failed.

    Kindly note that the 12 big tables which I mentioned above contains data which is actually not needed for my unit testing on development database.
    Is it possible to perform RMAN copy without including the data from these 12 tables?

    Or could you think of any other solution so that I can do "part" refresh of production database to developement.

    Let me know if you need any more information?

    Database: Oracle 11g -

    Thanks a lot in advance!!

  2. #2
    Join Date
    Mar 2007
    Ft. Lauderdale, FL
    I would probably resort to expdp and impdp to refresh development data.

    As posted all tables but the 12 large ones account for only 12% of the database so... here is what I would do.

    1- expdp database excluding the 12 large tables-- set #1
    2- expdp a consistent set of partitions of the 12 large tables-- set #2
    3- pre-create database in development environment, be sure all tables are empty (truncate if needed)
    4- impdp set #1 then impdp set #2
    5- crack open a beer and be happy forever after.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.