Re: CLOB data
Thanks Mladen for your wonderful explanation. Nancy From: Mladen Gogala <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: CLOB data Date: Thu, 16 Oct 2003 20:24:25 -0800 But the main point is valid. The phenomenon observed by Nancy is caused by the storage in the row. On 2003.10.16 19:59, Paul Drake wrote: how about using tablespaces UMDOTBS (instead of UNDOTBS) and SYTSEM? thanks for the laugh. Pd Mladen Gogala <[EMAIL PROTECTED]> wrote: This is because "ENABLE STORAGE IN ROW" is the default, which means that oracle will store the first 4000 bytes in the original block. The first part of a LOB is, essentially, identical to VARCHAR2(4000) or VARRAW(4000). If you say something like CREATE TABLE t ( ISCT_ID NUMBER(10), CONTENT CLOB ) TABLESPACE UNDOTBS LOB(CONTENT) STORE AS LOB_CONTENTS ( TABLESPACE SYSTEM DISABLE STORAGE IN ROW NOCACHE LOGGING) / You will not be able to do that any longer. You cannot, however, exceed 4000 characters (I'm not sure what happens if the characters are multibyte ones - may be Steve or Cary could help with that). - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ See when your friends are online with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
CLOB data
I know Oracle provides us dbms_lob package to manipulate LOB data. However, I can use standard DML to manipulate CLOB data as following. Is there advantage to use dbms_lob package? SQL> CREATE TABLE t 2 ( 3ISCT_ID NUMBER(10), 4CONTENT CLOB 5 ); Table created. SQL> insert into t values(1, 'test'); 1 row created. SQL> insert into t values(2, NULL); 1 row created. SQL> update t set content='test2' where isct_id=2; 1 row updated. SQL> select * from t; ISCT_ID CONTENT --- 1 test 2 test2 SQL> _ Surf and talk on the phone at the same time with broadband Internet access. Get high-speed for as low as $29.95/month (depending on the local service providers in your area). https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: LONG data type in 10g
Oracle recommends others to use LOB instead of LONG. However they are lazy to change their own. From: K Gopalakrishnan <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: LONG data type in 10g Date: Thu, 16 Oct 2003 08:24:39 -0800 SQL*Plus: Release 10.1.0.0.0 - Beta on Thu Oct 16 20:35:19 2003 Copyright (c) 1982, 2003, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> startup; ORACLE instance started. Total System Global Area 170990484 bytes Fixed Size 760724 bytes Variable Size 121634816 bytes Database Buffers 25165824 bytes Redo Buffers 23429120 bytes Database mounted. Database opened. SQL> desc view$; Name Null?Type - OBJ# NOT NULL NUMBER AUDIT$NOT NULL VARCHAR2(38) COLS NOT NULL NUMBER INTCOLS NOT NULL NUMBER PROPERTY NOT NULL NUMBER FLAGS NOT NULL NUMBER TEXTLENGTH NUMBER TEXT LONG SQL> desc trigger$; Name Null?Type - OBJ# NOT NULL NUMBER TYPE# NOT NULL NUMBER UPDATE$ NOT NULL NUMBER INSERT$ NOT NULL NUMBER DELETE$ NOT NULL NUMBER BASEOBJECTNOT NULL NUMBER REFOLDNAME VARCHAR2(30) REFNEWNAME VARCHAR2(30) DEFINITION VARCHAR2(4000) WHENCLAUSE VARCHAR2(4000) ACTION#LONG ACTIONSIZE NUMBER ENABLEDNUMBER PROPERTY NOT NULL NUMBER SYS_EVTS NUMBER NTTRIGCOL NUMBER NTTRIGATT NUMBER REFPRTNAME VARCHAR2(30) ACTIONLINENO NUMBER --- Mladen Gogala <[EMAIL PROTECTED]> wrote: > Much more menacing question: > Are the columns DBA_VIEWS.TEXT and DBA_TRIGGERS.TRIGGER_BODY still of > > the type long? > > = Have a nice day !! Best Regards, K Gopalakrishnan, Bangalore, INDIA. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Add MSN 8 Internet Software to your current Internet access and enjoy patented spam control and more. Get two months FREE! http://join.msn.com/?page=dept/byoa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: LONG data type in 10g
Thanks for trying it out, Jonathan. Also I really appreciate all replies. From: Jonathan Gennick <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: LONG data type in 10g Date: Thu, 16 Oct 2003 07:09:26 -0800 Thursday, October 16, 2003, 10:44:26 AM, you wrote: NH> Does any one know if 10g still supports LONG data type? I just tried creating a table with a LONG column in Beta 2, and that worked, so I'd say the answer is yes. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word "subscribe" in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Express yourself with MSN Messenger 6.0 -- download now! http://www.msnmessenger-download.com/tracking/reach_general -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
LONG data type in 10g
Does any one know if 10g still supports LONG data type? _ Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: data loading
Thank Stephane and Jacques for your reply. We are running Oracle 8.1.7.4, but will upgrade to Oracle 9.2.0.3 soon. Partitioning works for some of our tables. Are there other methods that allow us to avoid the problem of unique constraint. From: "Jacques Kilchoer" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: data loading Date: Mon, 06 Oct 2003 14:29:24 -0800 If you choose to implement this method, make sure that all the tables can be in the exchange partition command. The exchange partition command has certain requirements to be able to complete the exchange (similarity of indexes and constraints), and the table has to be suitable for building a "copy" as a partitioned table. For example, the method would not work with this table (because of the two unique keys): create table t (a number unique, b number unique) ; If your database version is 8.0 there are even more restrictions on what kind of table you can use in an exchange partition command (IIRC any FK constraint will prevent you from using the table in an exchange partition.) > -Original Message- > Stephane Paquette > > You can use the partitionning option. > > Load into a 1 partitionned table then do a partition exchange with the > target table. > > It allows you to have the current data live while loading into the > partitionned table. > Once the load is loaded just exchange the partition with the > table, it is > fast as it is just an update in the data dictionnary. No data > is physically > moved. > > -Original Message- > Nancy Hu > > We have an Oracle database that is a kind of data warehouse. > We load data > from mainframe into the database every day. The following > are the steps how > we load data currently: > > 1. get the data file from Datacom > 2. ftp the data file from mainframe to the Sun machine where > the Oracle > database resides > 3. truncate all tables in Oracle database > 4. load the data into Oracle with SQL LOADER > > Most tables in the Oracle database don't have primary key. > Tables are not > available during the loading that gives us problem for our 24x7 > availability. Therefore, we would like to change our loading > method. Any > ideas would be highly appreciated. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Instant message in style with MSN Messenger 6.0. Download it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
data loading
Hi All, We have an Oracle database that is a kind of data warehouse. We load data from mainframe into the database every day. The following are the steps how we load data currently: 1. get the data file from Datacom 2. ftp the data file from mainframe to the Sun machine where the Oracle database resides 3. truncate all tables in Oracle database 4. load the data into Oracle with SQL LOADER Most tables in the Oracle database don't have primary key. Tables are not available during the loading that gives us problem for our 24x7 availability. Therefore, we would like to change our loading method. Any ideas would be highly appreciated. Nancy _ Instant message with integrated webcam using MSN Messenger 6.0. Try it now FREE! http://msnmessenger-download.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: archive old data
I really appreciate all replies. Those solutions are very good. I think partition works for our case. Nancy From: "Stephane Faroult" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: RE: archive old data Date: Fri, 12 Sep 2003 09:34:25 -0800 In an ideal world, tables are partitioned and you just have to archive partitions - either to other tables in an ARCHIVE schema, or by exporting them - and then you can truncate them (=partitions). In the real world, you always have some rows at a very old date which are *not* in the CLOSED state. Which means that partitioning on a state with row movement enabled is not necessarily to be frowned upon. If you don't have enormous amounts of data, CREATE TABLE AS SELECT to save the data to archive somewhere. Rather than deleting the rows afterwards, it's probably better to do a CREATE TABLE AS SELECT elsewhere with the rows you want to keep, then TRUNCATE the table, then reinsert the rows back - it will have the advantage of reorganizing and resetting the high-water mark. Of course you will have to juggle with constraints and triggers. If you feel lazy, just convince your management that since disk space is so cheap nowadays they can keep everything online. HTH SF >- --- Original Message --- - >From: "Nancy Hu" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Fri, 12 Sep 2003 08:49:31 > >We have some tables that have data for many years. >We are going to archive >the data that are older than 3 years. I would like >to find out how you guys >usually do this or a best way to do this. Thanks >for any inputs in advance. > >Nancy > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
archive old data
We have some tables that have data for many years. We are going to archive the data that are older than 3 years. I would like to find out how you guys usually do this or a best way to do this. Thanks for any inputs in advance. Nancy _ Fast, faster, fastest: Upgrade to Cable or DSL today! https://broadband.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: 9.2.0.4 anyone
n > >dump, this is the SGA parent heap for shared pool heaps). > > > > > > > > that makes the shared pool one big memory area. > > > > > > One of the "fixes" in 9.2.0.4 is to make this the default now (so I have > > > been told). > > > >Can't get my hands oon 9.2.0.2 or 0.3, but in 0.4 (on Windows), the > >_kghsidx_count defaults to 1 anyway, check it out on your systems. > > > >Tanel. > > > > > > > > > -Original Message- > > > > From: Stephen Lee > > > > Sent: Thursday, September 11, 2003 11:39 AM > > > > To: Multiple recipients of list ORACLE-L > > > > Subject: RE: 9.2.0.4 anyone > > > > > > > > > > > > > > > > Initial testing indicates that the bug(s) that caused index > > > > create/rebuild > > > > online to lock a table and then get permanently stuck in a > > > > hung state have > > > > been fixed. > > > > > > > > It looks like 9.2.0.4 does NOT fix the problem of fatal 4031 > > > > situations that > > > > can only be cleared by restarting the instance. So you are > > > > still stuck with > > > > by guess and by golly fiddling with the shared pool. > > > > > > > > > -Original Message- > > > > > > > > > > Anyone have any negative experiences with 9.2.0.4 yet? > > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Stephen Lee > > > > INET: [EMAIL PROTECTED] > > > > > > > > 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: [EMAIL PROTECTED] (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 ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Stephen Lee > > > INET: [EMAIL PROTECTED] > > > > > > 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: [EMAIL PROTECTED] (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 ORACLE-L FAQ: http://www.orafaq.net > >-- > >Author: Tanel Poder > > INET: [EMAIL PROTECTED] > > > >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: [EMAIL PROTECTED] (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). > > _ > Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage. > http://join.msn.com/?PAGE=features/es > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Nancy Hu > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY,
Re: 9.2.0.4 anyone
.orafaq.net > > -- > > Author: Stephen Lee > > INET: [EMAIL PROTECTED] > > > > 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: [EMAIL PROTECTED] (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 ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Lee > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). _ Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage. http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nancy Hu INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
