Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Clarification on CLOB column

Posted on 2014-09-24
10
Medium Priority
?
551 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 78

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 78

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

971 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