Home » SQL & PL/SQL » SQL & PL/SQL » Converting LONG to CLOB - ORACLE NOT CONECTED (Oracle 10.0.2 , HP-UX Itanium)
Converting LONG to CLOB - ORACLE NOT CONECTED [message #354487] Sun, 19 October 2008 18:38 Go to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member

Hi,

We are trying to convert the Long data type to CLOB datatype for a table which occupies around 150gb of table space. The script is running for quite some time around an hr. But its failing saying ORACLE NOT CONNECTED aftr dat. But when we try to connect to db using sqlplus, we are able to. Can some one throw some light on this

declare
v_exists number;
begin
select 1
into v_exists
from USER_TAB_COLUMNS
where TABLE_NAME = 'BILLDATA'
and COLUMN_NAME = 'DATA'
and DATA_TYPE = 'CLOB';
exception
when NO_DATA_FOUND then
execute immediate('
alter table BILLDATA modify
(
DATA CLOB
) LOB (DATA) STORE AS (CACHE)
');
end;
Re: Converting LONG to CLOB - ORACLE NOT CONECTED [message #354488 is a reply to message #354487] Sun, 19 October 2008 18:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Why use pl/sql and dynamic sql? Why not just the following?

alter table billdata modify (data clob);

If this is just a simplified example and you are trying to update multiple columns in multiple tables, then it might be better to use SQL to create SQL and spool the SQL commands to a .sql, so that you could just execute a script of SQL commands instead of a pl/sql block. This helps eliminate one layer of complexity and one source of potential errors and may make errors easier to diagnose.

[Updated on: Sun, 19 October 2008 18:55]

Report message to a moderator

Re: Converting LONG to CLOB - ORACLE NOT CONECTED [message #354489 is a reply to message #354488] Sun, 19 October 2008 19:24 Go to previous messageGo to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Hi Barbara,

This is just a snippet. However we have some other stored procedures to be executed along with this alter table . However I've tried this by running as normal sql. But still we face the same problem. Its saying oracle not connected exactly after an hour. Is there any setting in oracle that implicates the timeout causing this error or is there any thing set in unix??

Because we ran the same script earlier and it finished in 1.5hrs. But during our last 2runs we are facing this issue.
Re: Converting LONG to CLOB - ORACLE NOT CONECTED [message #354490 is a reply to message #354487] Sun, 19 October 2008 21:51 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
My experience with "not connected" is usually some bug in the oracle product. I can think of several reasons why your particular approach is giving your trouble:

Quote:
1) maybe you got a really long LONG
2) maybe there is a hardware problem like a bad spot on disk used as temp space, or a faulty memory chip where some temporary cache is being held during your convert
3) maybe there is some other obscure limit you have reached that we dont' know about
4) maybe you have simply taxed redo/undo too far
5) maybe you hit a privileges issue that deals with executing DDL from inside plsql

Who knows. Whenever I am faced with this particular error, I take two tacks:

Quote:
1) I contact Oracle with a TAR
2) not wanting to wait a week for a solution which will in all likely hood be "upgrade to version X and try again, if it is still an issue get back to us", I look for an alternative coding solution for what I want to do.

In your case #2 is a good idea. Here is snippet taken from the following URL

Oracle Conversion Functions

Quote:
desc user_triggers

CREATE TABLE lobtest (
testcol CLOB);

INSERT INTO lobtest
SELECT TO_LOB(trigger_body)
FROM user_triggers;


By creating a new table, you can work around issues like redo/undo, row expansion/contraction, and temp space allocations etc. Much easier on the Oracle infrastructure and you will likely be executing different pieces of the oracle kernel as aposed to using the ALTER method from inside PL/SQL. I might even consider create table as/no logging, or insert append with this one to speed things up.

If nothing else, doing it this way might be a help to Oracle in finding the issue for you.

And one more thing... next time you post code, please format it first.

Good luck, Kevin

[Updated on: Sun, 19 October 2008 21:54]

Report message to a moderator

Re: Converting LONG to CLOB - ORACLE NOT CONECTED [message #354664 is a reply to message #354490] Mon, 20 October 2008 12:40 Go to previous message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Hi Kevin,

Many thanks for the reply.
I'm goin for the second approach mentioned. I'm plannin to rename the table to some other name and trying to create a new table with CLOB datatype. This might not be an ideal solution. But our requirement will satisfy this to an extent. Thanks.
Previous Topic: Stored Procedure Creation and Execution Questions
Next Topic: SQl insert
Goto Forum:
  


Current Time: Sat Dec 10 12:47:28 CST 2016

Total time taken to generate the page: 0.08489 seconds