Solved

Clarification on CLOB column

Posted on 2014-09-24
10
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40342171
#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 74

Expert Comment

by:sdstuber
ID: 40342189
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40342193
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

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

Author Comment

by:sam_2012
ID: 40344657
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
 

Author Comment

by:sam_2012
ID: 40344663
Can I specify oracle to use different tablespace for CLOB column using alter table command.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40344676
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
ID: 40346768
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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40346780
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
ID: 40346822
awesome. Thanks a lot.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

752 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