-
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
-
Still waiting. Any help will be appreciated
-
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
-
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
-
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?
-
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
-
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
-
Queyon Zeng
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|