Informix Unload equivalent in Oracle

Hi, I'm an experienced Informix db admin. The company I work for purchased a system that uses Oracle for its backend a few years back. I need some help with Oracle admin related to unloading and reloading data. I'm struggling to find a utility that works like the unload command in informix to dump part of a table to an ascii file. Here's what I'm trying to do... I have tables that are partitioned. I need to unload all the rows from one of the partitions and then drop the partition and create more new partitions with different partition filters. After I have created the new partitions I want to reload the unloaded data rows and have them inserted into the new correct partitions. If I understood the system better I would probably just recreate all the tables with partitions as tables with a single partition. But I don't want to change too much and I really don't have the time for that. If there are any Oracle experts who can help point me in the right direction I would really appreciate it. This would be so easy in Informix and I'm at a loss for how to do it with Oracle. :) BTW... a potential area of concern is that some of the partitioned tables haves columns with the RAW data type. I would want to make sure that after I dump the RAW data it will be restored correctly when the data is reloaded. Thanks, Gregg Walker

There is no CSV style unload in Oracle. If you must do this via an 'unload', look at import and export (imp/exp). You could perhaps export Partition X (from Table A), create a new partitioned table with the new partitions you want, import into this table, and then swap these partitions back into Table A. Although I'm also not really sure why you are unloading the table at all ? Why don't you just split the partition ? Or switch the partition out, do multiple CTAS with the required filters to get your new partitions, and then switch these new tables back in as the new partitions ? The storage cost is going to be about the same during the exercise. Gregg Walker wrote: > Hi, > > I'm an experienced Informix db admin. The company I work for purchased a > system that uses Oracle for its backend a few years back. I need some help > with Oracle admin related to unloading and reloading data. > > I'm struggling to find a utility that works like the unload command in > informix to dump part of a table to an ascii file. > > Here's what I'm trying to do... > > I have tables that are partitioned. I need to unload all the rows from one > of the partitions and then drop the partition and create more new partitions > with different partition filters. After I have created the new partitions I > want to reload the unloaded data rows and have them inserted into the new > correct partitions. > > If I understood the system better I would probably just recreate all the > tables with partitions as tables with a single partition. But I don't want > to change too much and I really don't have the time for that. > > If there are any Oracle experts who can help point me in the right direction > I would really appreciate it. This would be so easy in Informix and I'm at > a loss for how to do it with Oracle. :) > > BTW... a potential area of concern is that some of the partitioned tables > haves columns with the RAW data type. I would want to make sure that after > I dump the RAW data it will be restored correctly when the data is reloaded. > > Thanks, > Gregg Walker > >

Hi Mark, > If you must do this via an 'unload', look at import and export (imp/exp). > You could perhaps export Partition X (from Table A), create a new > partitioned table with the new partitions you want, import into this > table, and then swap these partitions back into Table A. This sounds like a lot of tedious work since I have about 20 tables that need to have this done. > Although I'm also not really sure why you are unloading the table at all ? Me either. It's my Informix mentality... :) My problem with the Oracle tables is that they have begun using the last partition which is meant to be an overflow and it's date range filter will hold more data than the hard drives will allow. Does that make sense? > Why don't you just split the partition ? Or switch the partition out I didn't know you could do that but splitting definitely sounds like what I want to do. We're using Oracle 9.2 so I would assume that splitting partitions is supported. What do you mean by switching a partition out? Doesn't sound like what I need to do but maybe you could enlighten me on this one. Thanks much for your response. Sincerely, Gregg Walker "Mark Townsend" <markbtownsend@comcast.net> wrote in message news:-vidnczc9aIjtXrcRVn-oA@comcast.com... > There is no CSV style unload in Oracle. > > If you must do this via an 'unload', look at import and export (imp/exp). > You could perhaps export Partition X (from Table A), create a new > partitioned table with the new partitions you want, import into this > table, and then swap these partitions back into Table A. > > Although I'm also not really sure why you are unloading the table at all ? > Why don't you just split the partition ? Or switch the partition out, do > multiple CTAS with the required filters to get your new partitions, and > then switch these new tables back in as the new partitions ? The storage > cost is going to be about the same during the exercise. > > > Gregg Walker wrote: >> Hi, >> >> I'm an experienced Informix db admin. The company I work for purchased a >> system that uses Oracle for its backend a few years back. I need some >> help with Oracle admin related to unloading and reloading data. >> >> I'm struggling to find a utility that works like the unload command in >> informix to dump part of a table to an ascii file. >> >> Here's what I'm trying to do... >> >> I have tables that are partitioned. I need to unload all the rows from >> one of the partitions and then drop the partition and create more new >> partitions with different partition filters. After I have created the >> new partitions I want to reload the unloaded data rows and have them >> inserted into the new correct partitions. >> >> If I understood the system better I would probably just recreate all the >> tables with partitions as tables with a single partition. But I don't >> want to change too much and I really don't have the time for that. >> >> If there are any Oracle experts who can help point me in the right >> direction I would really appreciate it. This would be so easy in >> Informix and I'm at a loss for how to do it with Oracle. :) >> >> BTW... a potential area of concern is that some of the partitioned tables >> haves columns with the RAW data type. I would want to make sure that >> after I dump the RAW data it will be restored correctly when the data is >> reloaded. >> >> Thanks, >> Gregg Walker >

Gregg Walker wrote: > Hi Mark, > > >>If you must do this via an 'unload', look at import and export (imp/exp). >>You could perhaps export Partition X (from Table A), create a new >>partitioned table with the new partitions you want, import into this >>table, and then swap these partitions back into Table A. > > > This sounds like a lot of tedious work since I have about 20 tables that > need to have this done. > > >>Although I'm also not really sure why you are unloading the table at all ? > > > Me either. It's my Informix mentality... :) My problem with the Oracle > tables is that they have begun using the last partition which is meant to be > an overflow and it's date range filter will hold more data than the hard > drives will allow. Does that make sense? > > >>Why don't you just split the partition ? Or switch the partition out > > > I didn't know you could do that but splitting definitely sounds like what I > want to do. We're using Oracle 9.2 so I would assume that splitting > partitions is supported. > > What do you mean by switching a partition out? Doesn't sound like what I > need to do but maybe you could enlighten me on this one. > > Thanks much for your response. > > Sincerely, > Gregg Walker > > "Mark Townsend" <markbtownsend@comcast.net> wrote in message > news:-vidnczc9aIjtXrcRVn-oA@comcast.com... > >>There is no CSV style unload in Oracle. >> >>If you must do this via an 'unload', look at import and export (imp/exp). >>You could perhaps export Partition X (from Table A), create a new >>partitioned table with the new partitions you want, import into this >>table, and then swap these partitions back into Table A. >> >>Although I'm also not really sure why you are unloading the table at all ? >>Why don't you just split the partition ? Or switch the partition out, do >>multiple CTAS with the required filters to get your new partitions, and >>then switch these new tables back in as the new partitions ? The storage >>cost is going to be about the same during the exercise. >> >> >>Gregg Walker wrote: >> >>>Hi, >>> >>>I'm an experienced Informix db admin. The company I work for purchased a >>>system that uses Oracle for its backend a few years back. I need some >>>help with Oracle admin related to unloading and reloading data. >>> >>>I'm struggling to find a utility that works like the unload command in >>>informix to dump part of a table to an ascii file. >>> >>>Here's what I'm trying to do... >>> >>>I have tables that are partitioned. I need to unload all the rows from >>>one of the partitions and then drop the partition and create more new >>>partitions with different partition filters. After I have created the >>>new partitions I want to reload the unloaded data rows and have them >>>inserted into the new correct partitions. >>> >>>If I understood the system better I would probably just recreate all the >>>tables with partitions as tables with a single partition. But I don't >>>want to change too much and I really don't have the time for that. >>> >>>If there are any Oracle experts who can help point me in the right >>>direction I would really appreciate it. This would be so easy in >>>Informix and I'm at a loss for how to do it with Oracle. :) >>> >>>BTW... a potential area of concern is that some of the partitioned tables >>>haves columns with the RAW data type. I would want to make sure that >>>after I dump the RAW data it will be restored correctly when the data is >>>reloaded. >>> >>>Thanks, >>>Gregg Walker Forget unloading or even moving the data this can all be done with a few simple SQL statements real-time inside the database. Create a new partition as Mark suggests. Here's a link to a web site that contains examples of all of the code you will need to do it. http://www.psoug.org click on Morgan's Library click on Partitioning When in Informix it is best to think the Informix way. When in Oracle it will only make angst (and visa versa). -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace 'x' with 'u' to respond)

You want to split - ALTER TABLE xxx SPLIT PARTITION. See the doc at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#6810 Specifically see the section on how to optimize - basically you want to minimize data movement. As an example - assuming a partitioned table SALES with the last partition called sales_q1_2001 ALTER TABLE sales SPLIT PARTITION sales_q1_2001 AT (TO_DATE('01-APR-2001', 'DD-MON-YYYY')) INTO (PARTITION sales_q1_2001, PARTITION sales_beyond_q1_2001); In this case sales_q1_2001 was the MAXVALUE partition, which is split into 2 - sales_q1_2001, which is now upper bounded by the date '01-APR-2001', and sales_beyond_q1_2001, which is the new MAXVALUE partition. Note that if you have local indexes in place the indexes will be split as well and need to be rebuilt after the operation. Re: Switching tables/partitions. If a table has the same columns as a partitioned table, and a known range of values inside a partition key, then you can switch the table into (and out of) the partitioned table. See the doc at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#11985 For example (switching in) /* Creating an empty staging table. Sales is the partitioned table *? create table sales_delta nologging as select * from sales where 1=0; /* Load this table - using an external table as source */ INSERT /*+ APPEND */ INTO sales_delta SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, sum(QUANTITY_SOLD) quantity_sold, sum(AMOUNT_SOLD) amount_sold FROM SALES_DELTA_XT GROUP BY prod_id,time_id,cust_id,channel_id,promo_id; /* Analyse stats on new table */ exec dbms_stats.gather_table_stats('SH', 'sales_delta', estimate_percent=>20); /* Build indexes on new table */ CREATE BITMAP INDEX sales_prod_local_bix ON sales_delta (prod_id) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAP INDEX sales_cust_local_bix ON sales_delta (cust_id) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAP INDEX sales_time_local_bix ON sales_delta (time_id) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAP INDEX sales_channel_local_bix ON sales_delta (channel_id) NOLOGGING COMPUTE STATISTICS ; CREATE BITMAP INDEX sales_promo_local_bix ON sales_delta (promo_id) NOLOGGING COMPUTE STATISTICS ; /* Enable contsraints on new table */ ALTER TABLE sales_delta ADD ( CONSTRAINT sales_product_delta_fk FOREIGN KEY (prod_id) REFERENCES products RELY ENABLE NOVALIDATE , CONSTRAINT sales_customer_delta_fk FOREIGN KEY (cust_id) REFERENCES customers RELY ENABLE NOVALIDATE , CONSTRAINT sales_time_delta_fk FOREIGN KEY (time_id) REFERENCES times RELY ENABLE NOVALIDATE , CONSTRAINT sales_channel_delta_fk FOREIGN KEY (channel_id) REFERENCES channels RELY ENABLE NOVALIDATE , CONSTRAINT sales_promo_delta_fk FOREIGN KEY (promo_id) REFERENCES promotions RELY ENABLE NOVALIDATE ) ; /* Add empty partition to sales table */ ALTER TABLE salesADD PARTITION sales_q1_2001 VALUES LESS THAN (MAXVALUE); /* Switch staging table in as new partition */ ALTER TABLE sales EXCHANGE PARTITION sales_q1_2001 WITH TABLE sales_delta INCLUDING INDEXES; You can also do the opposite to roll data out. Gregg Walker wrote: > Hi Mark, > > >>If you must do this via an 'unload', look at import and export (imp/exp). >>You could perhaps export Partition X (from Table A), create a new >>partitioned table with the new partitions you want, import into this >>table, and then swap these partitions back into Table A. > > > This sounds like a lot of tedious work since I have about 20 tables that > need to have this done. > > >>Although I'm also not really sure why you are unloading the table at all ? > > > Me either. It's my Informix mentality... :) My problem with the Oracle > tables is that they have begun using the last partition which is meant to be > an overflow and it's date range filter will hold more data than the hard > drives will allow. Does that make sense? > > >>Why don't you just split the partition ? Or switch the partition out > > > I didn't know you could do that but splitting definitely sounds like what I > want to do. We're using Oracle 9.2 so I would assume that splitting > partitions is supported. > > What do you mean by switching a partition out? Doesn't sound like what I > need to do but maybe you could enlighten me on this one. > > Thanks much for your response. > > Sincerely, > Gregg Walker > > "Mark Townsend" <markbtownsend@comcast.net> wrote in message > news:-vidnczc9aIjtXrcRVn-oA@comcast.com... > >>There is no CSV style unload in Oracle. >> >>If you must do this via an 'unload', look at import and export (imp/exp). >>You could perhaps export Partition X (from Table A), create a new >>partitioned table with the new partitions you want, import into this >>table, and then swap these partitions back into Table A. >> >>Although I'm also not really sure why you are unloading the table at all ? >>Why don't you just split the partition ? Or switch the partition out, do >>multiple CTAS with the required filters to get your new partitions, and >>then switch these new tables back in as the new partitions ? The storage >>cost is going to be about the same during the exercise. >> >> >>Gregg Walker wrote: >> >>>Hi, >>> >>>I'm an experienced Informix db admin. The company I work for purchased a >>>system that uses Oracle for its backend a few years back. I need some >>>help with Oracle admin related to unloading and reloading data. >>> >>>I'm struggling to find a utility that works like the unload command in >>>informix to dump part of a table to an ascii file. >>> >>>Here's what I'm trying to do... >>> >>>I have tables that are partitioned. I need to unload all the rows from >>>one of the partitions and then drop the partition and create more new >>>partitions with different partition filters. After I have created the >>>new partitions I want to reload the unloaded data rows and have them >>>inserted into the new correct partitions. >>> >>>If I understood the system better I would probably just recreate all the >>>tables with partitions as tables with a single partition. But I don't >>>want to change too much and I really don't have the time for that. >>> >>>If there are any Oracle experts who can help point me in the right >>>direction I would really appreciate it. This would be so easy in >>>Informix and I'm at a loss for how to do it with Oracle. :) >>> >>>BTW... a potential area of concern is that some of the partitioned tables >>>haves columns with the RAW data type. I would want to make sure that >>>after I dump the RAW data it will be restored correctly when the data is >>>reloaded. >>> >>>Thanks, >>>Gregg Walker >> > >

Thanks for your help everyone. I know what I need to do now. Cheers, Gregg Walker "Mark Townsend" <markbtownsend@comcast.net> wrote in message news:41E76CCD.5010305@comcast.net... > You want to split - ALTER TABLE xxx SPLIT PARTITION. See the doc at > http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#6810 > Specifically see the section on how to optimize - basically you want to > minimize data movement. > > As an example - assuming a partitioned table SALES with the last > partition called sales_q1_2001 > > ALTER TABLE sales > SPLIT PARTITION sales_q1_2001 > AT (TO_DATE('01-APR-2001', 'DD-MON-YYYY')) > INTO (PARTITION sales_q1_2001, > PARTITION sales_beyond_q1_2001);> > In this case sales_q1_2001 was the MAXVALUE partition, which is split into > 2 - sales_q1_2001, which is now upper bounded by the date '01-APR-2001', > and sales_beyond_q1_2001, which is the new MAXVALUE partition. > > Note that if you have local indexes in place the indexes will be split as > well and need to be rebuilt after the operation. > > Re: Switching tables/partitions. If a table has the same columns as a > partitioned table, and a known range of values inside a partition key, > then you can switch the table into (and out of) the partitioned table. > > See the doc at > http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/partiti.htm#11985 > > For example (switching in) > > /* Creating an empty staging table. Sales is the partitioned table *? > > create table sales_delta nologging as > select * > from sales > where 1=0;> > /* Load this table - using an external table as source */ > > INSERT /*+ APPEND */ INTO sales_delta > SELECT /*+ PARALLEL (SALES_DELTA_XT,4) */ > PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, > sum(QUANTITY_SOLD) quantity_sold, > sum(AMOUNT_SOLD) amount_sold > FROM SALES_DELTA_XT > GROUP BY prod_id,time_id,cust_id,channel_id,promo_id; > > /* Analyse stats on new table */ > > exec dbms_stats.gather_table_stats('SH', 'sales_delta', > estimate_percent=>20); > > /* Build indexes on new table */ > > CREATE BITMAP INDEX sales_prod_local_bix > ON sales_delta (prod_id) > NOLOGGING COMPUTE STATISTICS ; > CREATE BITMAP INDEX sales_cust_local_bix > ON sales_delta (cust_id) > NOLOGGING COMPUTE STATISTICS ; > CREATE BITMAP INDEX sales_time_local_bix > ON sales_delta (time_id) > NOLOGGING COMPUTE STATISTICS ; > CREATE BITMAP INDEX sales_channel_local_bix > ON sales_delta (channel_id) > NOLOGGING COMPUTE STATISTICS ; > CREATE BITMAP INDEX sales_promo_local_bix > ON sales_delta (promo_id) > NOLOGGING COMPUTE STATISTICS ; > > /* Enable contsraints on new table */ > > ALTER TABLE sales_delta > ADD ( CONSTRAINT sales_product_delta_fk > FOREIGN KEY (prod_id) > REFERENCES products RELY ENABLE NOVALIDATE > , CONSTRAINT sales_customer_delta_fk > FOREIGN KEY (cust_id) > REFERENCES customers RELY ENABLE NOVALIDATE > , CONSTRAINT sales_time_delta_fk > FOREIGN KEY (time_id) > REFERENCES times RELY ENABLE NOVALIDATE > , CONSTRAINT sales_channel_delta_fk > FOREIGN KEY (channel_id) > REFERENCES channels RELY ENABLE NOVALIDATE > , CONSTRAINT sales_promo_delta_fk > FOREIGN KEY (promo_id) > REFERENCES promotions RELY ENABLE NOVALIDATE > ) ;> > /* Add empty partition to sales table */ > > ALTER TABLE sales> ADD PARTITION sales_q1_2001 > VALUES LESS THAN (MAXVALUE); > > /* Switch staging table in as new partition */ > > ALTER TABLE sales EXCHANGE PARTITION sales_q1_2001 > WITH TABLE sales_delta INCLUDING INDEXES;> > You can also do the opposite to roll data out. > > > Gregg Walker wrote: >> Hi Mark, >> >> >>>If you must do this via an 'unload', look at import and export (imp/exp). >>>You could perhaps export Partition X (from Table A), create a new >>>partitioned table with the new partitions you want, import into this >>>table, and then swap these partitions back into Table A. >> >> >> This sounds like a lot of tedious work since I have about 20 tables that >> need to have this done. >> >> >>>Although I'm also not really sure why you are unloading the table at all >>>? >> >> >> Me either. It's my Informix mentality... :) My problem with the Oracle >> tables is that they have begun using the last partition which is meant to >> be an overflow and it's date range filter will hold more data than the >> hard drives will allow. Does that make sense? >> >> >>>Why don't you just split the partition ? Or switch the partition out >> >> >> I didn't know you could do that but splitting definitely sounds like what >> I want to do. We're using Oracle 9.2 so I would assume that splitting >> partitions is supported. >> >> What do you mean by switching a partition out? Doesn't sound like what I >> need to do but maybe you could enlighten me on this one. >> >> Thanks much for your response. >> >> Sincerely, >> Gregg Walker >> >> "Mark Townsend" <markbtownsend@comcast.net> wrote in message >> news:-vidnczc9aIjtXrcRVn-oA@comcast.com... >> >>>There is no CSV style unload in Oracle. >>> >>>If you must do this via an 'unload', look at import and export (imp/exp). >>>You could perhaps export Partition X (from Table A), create a new >>>partitioned table with the new partitions you want, import into this >>>table, and then swap these partitions back into Table A. >>> >>>Although I'm also not really sure why you are unloading the table at all >>>? Why don't you just split the partition ? Or switch the partition out, >>>do multiple CTAS with the required filters to get your new partitions, >>>and then switch these new tables back in as the new partitions ? The >>>storage cost is going to be about the same during the exercise. >>> >>> >>>Gregg Walker wrote: >>> >>>>Hi, >>>> >>>>I'm an experienced Informix db admin. The company I work for purchased >>>>a system that uses Oracle for its backend a few years back. I need some >>>>help with Oracle admin related to unloading and reloading data. >>>> >>>>I'm struggling to find a utility that works like the unload command in >>>>informix to dump part of a table to an ascii file. >>>> >>>>Here's what I'm trying to do... >>>> >>>>I have tables that are partitioned. I need to unload all the rows from >>>>one of the partitions and then drop the partition and create more new >>>>partitions with different partition filters. After I have created the >>>>new partitions I want to reload the unloaded data rows and have them >>>>inserted into the new correct partitions. >>>> >>>>If I understood the system better I would probably just recreate all the >>>>tables with partitions as tables with a single partition. But I don't >>>>want to change too much and I really don't have the time for that. >>>> >>>>If there are any Oracle experts who can help point me in the right >>>>direction I would really appr