Clarification on CLOB column

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.
sam_2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
#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
sdstuberCommented:
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
slightwv (䄆 Netminder) Commented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

sdstuberCommented:
slightwv is correct you really should keep your questions to one question per question.
0
sam_2012Author Commented:
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
sam_2012Author Commented:
Can I specify oracle to use different tablespace for CLOB column using alter table command.
0
slightwv (䄆 Netminder) Commented:
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
sam_2012Author Commented:
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
slightwv (䄆 Netminder) Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sam_2012Author Commented:
awesome. Thanks a lot.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.