Just an FYI ... enabling this feature does not go back and fix all of the tables that are storing your data "Out of Row".... BMC has an oracle stored procedure for fixing that. See page 10 and 11 of the "Using Oracle CLOBs with BMC Remedy Action Request System" white paper. Doing so will reduce your db size considerably (assuming you enabled it after installing ITSM).
Thanks, Sean From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Thad Esser Sent: Monday, February 13, 2012 3:26 PM To: arslist@ARSLIST.ORG Subject: Re: Question about "Oracle-Clob-Storage-In-Row" setting ** I was just testing that scenario as you responded. I created a new regular form, adding a character field with db length 8000 before the first save. This is what showed in the logs (clipped for brevity) when I did save it: CREATE TABLE T2011 (C1 varchar(15) not null,C2 varchar(254) NULL,C3 number(15,0) not null,C4 varchar(254) NULL,C5 varchar(254) not null,C6 number(15,0) not null,C7 number(15,0) not null,C8 varchar(254) not null) OK CREATE UNIQUE INDEX IT2011 ON T2011 (C1) OK CREATE TABLE H2011 (entryId varchar(15) not null,T0 number(15,0) NULL,U0 varchar(254) NULL,T1 number(15,0) NULL,U1 varchar(254) NULL,T2 number(15,0) NULL,U2 varchar(254) NULL,T3 number(15,0) NULL,U3 varchar(254) NULL,T4 number(15,0) NULL,U4 varchar(254) NULL) OK ... <creates H, T, B tables and views> ... ALTER TABLE T2011 ADD (C536870913 clob NULL) Still no indication that it is accounting for the storage in row. I'm sure it's there, I would just like to "see" it happening or be able to verify it somehow. Thanks, Thad On Mon, Feb 13, 2012 at 12:04 PM, Grooms, Frederick W <frederick.w.gro...@xo.com<mailto:frederick.w.gro...@xo.com>> wrote: You will only see the clause if you add a 0 length character field (or a Diary or a field bigger than 4000 characters) as those are stored as a CLOB. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>] On Behalf Of Thad Esser Sent: Monday, February 13, 2012 1:50 PM To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG> Subject: Question about "Oracle-Clob-Storage-In-Row" setting ** Hello, We are setting up a new 7.6.04 sandbox server (ARS on AIX, db is Oracle 11gR2) and reviewing our settings from prior versions. We've set "Oracle-Clob-Storage-In-Row: T" in the ar.conf. My understanding from reading the documents was that this setting causes ARS to add a clause to the CREATE TABLE statement so that clobs are stored in row. In the "Using Oracle CLOBs with BMC Remedy Action Request System" (March 2009, 89963) white paper it says: "To specify the storage option at the Oracle database level, use the ENABLE|DISABLE STORAGE IN ROW clause of the CREATE TABLE statement." So with the setting set and sql logging turned on, I created a regular form, core fields only. This is the entire CREATE TABLE statement from the log: CREATE TABLE T2005 (C1 varchar(15) not null,C2 varchar(254) NULL,C3 number(15,0) not null,C4 varchar(254) NULL,C5 varchar(254) not null,C6 number(15,0) not null,C7 number(15,0) not null,C8 varchar(254) not null) Shouldn't there be a part of that that says "STORAGE IN ROW" or something similar? I tried googling for examples of what the SQL should look like, but didn't find anything definitive. Is there a way to see if STORAGE IN ROW has been set for that table? Out of curiousity, I ran the same test on our existing 7.1 dev server which has been running with that setting for years and got the same results. So I figure I'm misunderstanding something and would appreciate any insights. Thanks, Thad _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org<http://www.arslist.org/> attend wwrug12 www.wwrug12.com<http://www.wwrug12.com/> ARSList: "Where the Answers Are" _attend WWRUG12 www.wwrug.com<http://www.wwrug.com> ARSlist: "Where the Answers Are"_ _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"