DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: urgent partition question.Please help

  1. #1
    Join Date
    Mar 2001
    Posts
    82

    Question

    Hi ALL,
    I have table called (SAN) in both my PRODUCTION
    database and my DEVELOPMENT database. The table
    has been partitioned based on months. I want to
    export the partition(SAN_AUG2000) from my production
    database. Then truncate/drop(which one is better?)
    the partition(SAN_AUG2000)in my development database.
    Then import the the partition(SAN_AUG2000)from the
    earlier export from PRODUCTION into DEVELOPMENT.


    My question is, do I just drop the partition from
    DEVELOPMENT e.g.alter table SAN drop partition SAN_AUG200.
    Then import the partiton SAN_AUG2000 into the development
    database? Or there is a work around it.
    I mean does this have to include splitting the partition,etc
    Or it's a straight forward shot?

    Please if possible explain the steps with syntax.
    Thanks.

    QUERY OUTPUT FROM DBA_SEGMNETS

    SEGMENT_NA PARTITION_NAME TABLESPACE_NAME
    ---------- ------------ ---------------
    SAN SAN_JUL2000 SAN_TBS_SAN_JUL2000
    SAN SAN_JUN2000 SAN_TBS_SAN_JUN2000
    SAN SAN_AUG2000 SAN_TBS_SAN_AUG2000
    SAN SAN_SEP2000 SAN_TBS_SAN_SEP2000
    SAN SAN_OCT2000 SAN_TBS_SAN_OCT2000
    SAN SAN_NOV2000 SAN_TBS_SAN_NOV2000

  2. #2
    Join Date
    Mar 2001
    Posts
    82
    Still waiting. Any help will be appreciated

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    There are a couple of ways you can do it:

    Exchange partitions:
    1. exchange SAN_AUG2000 with a temporary table from your production database
    2. export the temporary table from your production database
    3. import the temporary table to your development database
    4. truncate the development SAN_AUG2000 partition
    5. exchange the temporary table to the SAN_AUG2000 partition.

    Export Data:
    1. export SAN_AUG2000 data from production
    2. truncate SAN_AUG200 partition in development
    3. import SAN_AUG2000 data from export file
    Jeff Hunter

  4. #4

    Angry Export Partition

    I don't think you can export only a partition.
    You have to export the whole table and import it as a table.
    Queyon Zeng

  5. #5
    Join Date
    Mar 2001
    Posts
    82
    Thanks for the responses. QUEYON. Yes you can export only a partition.

    Jeff Hunter, Since the partition is SAN_AUG2000 and there are some partition after august 2000 namely SAN_SEP2000,
    SAN_OCT2000,SAN_NOV2000, is it not going to be a problem
    when I truncate the partition SAN_AUG2000 in development and try to import the partitioned table from production into development?

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Export Partition

    Originally posted by Queyon
    I don't think you can export only a partition.
    You have to export the whole table and import it as a table.
    No, no, no, no, no. You can export a single partition. See http://technet.oracle.com/docs/produ...ch01.htm#24977
    Jeff Hunter

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    No, that will not be a problem. When you truncate the partition, you are truncating only that partition. If you have some global or regular indexes, you will have to rebuild them. The partition level truncate will not affect the other partitions.

    The partition level import will not affect the other partitions either (assuming you have your partition range setup correctly). Remember, you are only inserting data into the table. Oracle will figure out where it belongs.
    Jeff Hunter

  8. #8

    Talking

    Thanks a lot.
    Queyon Zeng

  9. #9
    Join Date
    Mar 2001
    Posts
    82
    Thanks Jeff and the rest,
    I thought during the import all indexes(either global or regular) from the production environment will be imported as well, as far as they were exported.
    I thought the partition level import works just like table level import. Remember that in table level import, all objects like indexes, constraints, views, triggers,etc are imported as well so far as they were exported.

    I thought that same principle works for partition level export and import?

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    It's not the import that will mess you indexes up, it's the truncate.
    Jeff Hunter

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