Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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.
0
sam_2012
Asked:
sam_2012
  • 4
  • 4
  • 2
1 Solution
 
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
sam_2012Author Commented:
awesome. Thanks a lot.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now