Solved

Clarification on CLOB column

Posted on 2014-09-24
10
529 Views
Last Modified: 2014-09-26
Hi ,

I have an table called comments in the users_tab tablespace.  We have decided to alter one of its column called
comments_desc from varchar2 to CLOB.

We have decided to use th CLOB data type for storing the comments.  One technical question in regard to implementing it

1. We have decided to store the CLOB data in an tablespace other then the commentary table tablespace. I.e commentary table is in users_tab tablespace , we decided to create an new tablespace USER_LOB . My question here is how can i tell oracle to store the CLOB data in users_lob tablespace while altering the column comments_desc to clob datatype .


2. difference between regexp_like and like in oracle

3. While changing column data type can we instruct oracle to use the secure file features.
0
Comment
Question by:sam_2012
  • 4
  • 4
  • 2
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
#2: needs to be a new question.

3: what secure file features are you talking about?

securefile lobs aren't really 'secure' when it comes to typical usage of 'secure'.

#1: provide lob storage parameters.  I'll wait for the answer to my question before providing syntax.

There is also a lob index that goes with clobs and you will want to assign that to the different tablespace also.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
1 -

create table yourtab(yourcolumn clob) lob(yourcolumn) store as (tablespace user_lob);

2 -
xx like '%abc%'   --  like only supports positional wildcarding  - leading wildcards negate indexes

regexp_like(xx,'abc')  -- supports a wide variety of regular expression pattern matching, - most usage would negate indexes

3 -

yes
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Here is a securefile CLOB table that uses high compression and stores the lob inline.

You may or may not want ALL these options:
drop table new_tab purge;
create table new_tab(col1 clob)
LOB(col1) STORE AS SECUREFILE tab1_clob_segment (
                        COMPRESS HIGH
                  TABLESPACE USER_LOB ENABLE STORAGE IN ROW
                  INDEX tab1_clob_index
            )
;



Looks like dbms_redefinition is the way to go to actually perform the migration:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1770086700346491686
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
slightwv is correct you really should keep your questions to one question per question.
0
 

Author Comment

by:sam_2012
Comment Utility
To answer , slightwv, yes we will store thr LOB index also in the new table space.  Also , we will not recreate the comments table , we will fire an alter statement to modify the column from varchar2 to clob and then tell oracle to store the clob data in user_lob tablespace.

I will raise an different question for 2 and 3.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:sam_2012
Comment Utility
Can I specify oracle to use different tablespace for CLOB column using alter table command.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
No need for a new question on #3.

>>Can I specify oracle to use different tablespace for CLOB column using alter table command.

The answer is 'maybe'.  You cannot alter the column directly with data in it (at least I couldn't figure out how with the CLOB storage options).

You might be able to use the work-around:
add the new CLOB column
update all the rows and set the new column = old column
drop the old column
rename the new column to the old name

As you can probably see, this is tough to do on a live system.
0
 

Author Comment

by:sam_2012
Comment Utility
As suggested if I add an new column new comments_new  , can I make it store the LOB values in the new tablespace created?

Some thing like below
Alter table comments add comment_new clob tablespace <>
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
Yes.  It is the same syntax as above just with an alter table command.

Below is a complete example.
What you need to be careful of is if there are and indexes, constraints, triggers, etc... on the original column.  You'll need to account for those before you use the create/update/drop/rename method.

That is where the dbms_redefinition method is probably a better option.

drop table new_tab purge;
create table new_tab(col1 varchar2(10));
insert into new_tab values('Hello');
commit;

alter table new_tab add col1_new clob
 LOB(col1_new) STORE AS SECUREFILE tab1_clob_segment (
                         COMPRESS HIGH
                   TABLESPACE USERS ENABLE STORAGE IN ROW
                   INDEX tab1_clob_index
             )
 ;

update new_tab set col1_new=col1;
commit;

alter table new_tab drop column col1;

alter table new_tab rename column col1_new to col1;

select col1 from new_tab;

Open in new window

0
 

Author Closing Comment

by:sam_2012
Comment Utility
awesome. Thanks a lot.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now