| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: archiving data
Sai,
Thanks for the document information. The documnt indeed points that the max size of a LONG is 64K, yet my testcase proved that more than 64K is copied without incident. My test case used Tru64 Unix 5.1A and 8.1.7.4.
Using your data, please copy the long columns and check if the data istruncated. If it is not, you may want to open up a TAR pointing out the error in the document.
Thanks.
Arup Nanda
www.proligence.com
thanks a bunch for this test case...it surely will help me a lot
sai
Arup Nanda <orarup_at_hotmail.com> wrote:
I just did a few tests with a LONG field in a table. Final Answer: data more
than 64K is properly loaded using COPY.
Test Setup
Used a plain text file, s.dat in unix with 97885 characters (97K) . Created a table LT3 with only one field COL1 LONG. Used SQL*Loader to load the data into the table. The controlfile looks like this
load data
infile 's.dat' "var 5"
into table lt3
(
col1 position(1:100000) char)
I placed a number 99000 in the beginning of the line 1 on the file s.dat to indicate the length. After loading to the table LT3, I created a table LT4 as follows
SQL> set long 99000
SQL> copy from ananda/tiger_at_anw1 create lt4 using select * from lt3
Then the long size was changed and I created two more tables
SQL> set long 64000
SQL> copy from ananda/tiger_at_anw1 create lt5 using select * from lt3
SQL> set long 80
SQL> copy from ananda/tiger_at_anw1 create lt6 using select * from lt3
Finally, I created a third table to hold the data in LOB format so that I can measure it.
SQL> set long 99000
SQL> insert into lt7 select 3, to_lob(col1) from lt3;
SQL> insert into lt7 select 4, to_lob(col1) from lt4;
SQL> insert into lt7 select 5, to_lob(col1) from lt5;
SQL> insert into lt7 select 6, to_lob(col1) from lt6;
SQL> commit;
SQL> select col1, dbms_lob.getlength(col2) from lt7;
COL1 DBMS_LOB.GETLENGTH(COL2)
4 rows selected.
The results speak for themselves. As you can see, the COPY command correctly copied data from one table to the other where the chunk was about 97K, more than the 64K limit you mentioned. But the key was setting the LONGSIZE parameter in SQL*Plus. When I set it a low value, like 80 b! ytes, the value was truncated.
Hope this helps in your archiving strategy.
Arup Nanda
www.proligence.com
> Sai,
>
> Where did you find that limitation of 64K? Although I admit I have not
used
> a long column of that size, but according to the fine manuals, the max
size
> of LONG column copied is 2 GB; actually 2,000,000,000 bytes, not 64K. You
> have to specify the size of long in your session using SET LONG 2000000000
> before attempting the copy command.
>
> Please let us know where you found that 64K limitation. The ohter thin you
> have to consider is that COPY is being depecrated in 10i, or whatever it
> will be called; but then again, I hope your application will have ceased
> using LONGs.
>
> Arup Nanda
> www.proligence.com
>
> ---! -- Original Message -----
> From: "Sai Selvaganesan"
> To: "Multiple recipients of list ORACLE-L"
> Sent: Saturday, May 31, 2003 6:29 PM
> Subject: Re: archiving data
>
>
> > but i think there is a sqlplus limitation of 64k and
> > any data longet than 64k will get truncated in this
> > case too..
> >
> > correct me if i am wrong,even if u set long to a very
> > high value,data more than 64k in lenght will get
> > truncated .
> >
> > sai
> >
> > --- Arup Nanda wrote:
> > > For situations like this you have the COPY command
> > > of SQL*Plus.
> > >
> > > Remember, it's a SQL*Plus comamnd like set, btitle,
> > > etc. not a sql command
> > > you can embed inside a pl/sql block. You could
> > > cr! eate a table similar in
> > > structure to main table and then polulate the data
> > >
> > > SQL> SET LONG 999999
> > > -- this is neededto set the max size of the long
> > > data; otherwise it gets
> > > truncated.
> > >
> > > COPY FROM SCHEMA_NAME/PASSWORD_at_CONNECTSTRING -
> > > APPEND HOLDINGTABLE -
> > > USING SELECT * FROM MAINTABLE WHERE DATE_COL <
> > > SYSDATE - 12*30
> > >
> > > Note the use of hyphens after the lines. SQL*PLus
> > > commands are expected to
> > > be in one line. Since I am continuing on to the
> > > next, I used the
> > > continuation character hyphen.
> > >
> > > This by default commits after all the rows are
> > > loaded. You can control the
> > > commit frequency by specifying two parameters
! > > >
> > > -- sets 100 records per array
> > > SET ARRAYSIZE 100
> > > -- sets a commit to occur after every 200 batches,
> > > or 20,000 records
> > > SET COPYCOMMIT 200
> > >
> > > This process is fairly simple and can be easily
> > > automated using a shell
> > > script. Any error raised by the sql block can be
> > > checked.
> > >
> > > Hope this helps.
> > >
> > > Arup Nanda
> > > www.proligence.com
> > >
> > >
> > >
> > > ----- Original Message -----
> > > To: "Multiple recipients of list ORACLE-L"
> > >
> > > Sent: Friday, May 30, 2003 7:04 PM
> > >
> > >
> > > > hi there is this project that is going on for
> > > > archiving! old data from oltp system that is older
> > > than
> > > > 12 months and then purging them in the main db.
> > > >
> > > > the tables that are to be archived are with long
> > > rows.
> > > > they cannot be converted to lobs since this is a
> > > third
> > > > party application. here is where the problem lies.
> > > > oracle support when contacted says either mv to
> > > lobs
> > > > to make this move easier or use oci ..blah.blah..
> > > to
> > > > get this working if you want to remain in longs.
> > > >
> > > > there are some options i have though about:
> > > > 1. export /import ..but should make this highly
> > > > automated since the main db and archival db will
> > > be on
> > > > different hosts, this will ! not be monitored and
> > > import
> > > > has to go thru w/o issues etc.
> > > > 2. create snapshot - but they dont work with
> > > > long..hence not an option.
> > > > 3. getting sqlldr to work but i think it has that
> > > 32k
> > > > column size limitation.
> > > >
> > > >
> > > > so can you please suggest me whetehr there is
> > > > something else i can do or option 1 is the best
> > > given
> > > > the environment. the oracle is 8.1.7.2 on sun 2.8.
> > > >
> > > > thanks
> > > > sai
> > > > --
> > > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.net
> > > > --
> > > > Author: Sai Selvaganesan
> > > > INET: ssaisundar_at_sbcglobal.net
> >! > >
> > > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > > San Diego, California -- Mailing list and
> > > web hosting services
> > > >
> > >
> > ---------------------------------------------------------------------
> > > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > > (or the name of mailing list you want to be
> > > removed from). You may
> > > > also send the HELP command for other information
> > > (like subscribing).
> > > >
> > > >
> > > --
> > > Please see the official OR! ACLE-L FAQ:
> > > http://www.orafaq.net
> > > --
> > > Author: Arup Nanda
> > > INET: orarup_at_hotmail.com
> > >
> > > Fat City Network Services -- 858-538-5051
> > > http://www.fatcity.com
> > > San Diego, California -- Mailing list and web
> > > hosting services
> > >
> > ---------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an
> > > E-Mail message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of
> > > 'ListGuru') and in
> > > the message BODY, include a line containing: UNSUB
> > > ORACLE-L
> > > (or the name of mailing list you want to be removed
> > > from). You may
> > > also send the HELP command for other informationFat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Arup Nanda INET: orarup_at_hotmail.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Sun Jun 01 2003 - 08:49:42 CDT
 |
 |