Wow... I actually have something to disagree with Axton on ...
If you create a form (since v6x) Remedy no longer uses the BtttCccccccc
structure to hold the long character fields. I am on 7.1.0 patch 7 with Oracle
10g and all of my CLOB columns are just columns in the normal table.
SQL> describe T10;
Name Null? Type
------------ -------- ---------------
C1 NOT NULL VARCHAR2(15)
C2 VARCHAR2(254)
C3 NOT NULL NUMBER(15)
C4 VARCHAR2(254)
C5 NOT NULL VARCHAR2(254)
C6 NOT NULL NUMBER(15)
C7 NOT NULL NUMBER(15)
C8 NOT NULL VARCHAR2(128)
C536870912 VARCHAR2(255)
C536870913 CLOB
C536870914 NUMBER(15)
C536870915 CLOB
C536870916 NUMBER(15)
C536870921 VARCHAR2(4000)
C536870924 VARCHAR2(4000)
C536870925 NUMBER(15)
C540000710 VARCHAR2(255)
And if I look at the create script thru TOAD I see:
CREATE TABLE T10
(
C1 VARCHAR2(15 BYTE) NOT NULL,
C2 VARCHAR2(254 BYTE),
C3 NUMBER(15) NOT NULL,
C4 VARCHAR2(254 BYTE),
C5 VARCHAR2(254 BYTE) NOT NULL,
C6 NUMBER(15) NOT NULL,
C7 NUMBER(15) NOT NULL,
C8 VARCHAR2(128 BYTE) NOT NULL,
C536870912 VARCHAR2(255 BYTE),
C536870913 CLOB,
C536870914 NUMBER(15),
C536870915 CLOB,
C536870916 NUMBER(15),
C536870921 VARCHAR2(4000 BYTE),
C536870924 VARCHAR2(4000 BYTE),
C536870925 NUMBER(15),
C540000710 VARCHAR2(255 BYTE)
)
TABLESPACE ARSYSTEM
LOGGING
NOCOMPRESS
LOB (C536870913) STORE AS
( TABLESPACE ARSYSTEM
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
)
LOB (C536870915) STORE AS
( TABLESPACE ARSYSTEM
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
)
NOCACHE
NOPARALLEL
MONITORING;
The Oracle query for schemaid 10 for me is:
select SEGMENT_NAME, bytes
from USER_SEGMENTS
Where SEGMENT_NAME in (select SEGMENT_NAME from USER_LOBS where TABLE_NAME
like '_10')
or SEGMENT_NAME like '_10'
or SEGMENT_NAME like '_10C%'
NOTE: I used USER_SEGMENTS and USER_LOBS in case your DBA has locked down your
database (and the DBA_ versions of these objects are not available)
Fred
-----Original Message-----
From: Action Request System discussion list(ARSList)
[mailto:arsl...@arslist.org] On Behalf Of Axton
Sent: Wednesday, November 18, 2009 10:24 AM
To: arslist@ARSLIST.ORG
Subject: Re: Oracle in-row LOB storage issue - IM 7.03
You can look at the oracle data dictionary view dba_segments to see
the size of the base tables, lob segments, indexes, etc:
Here is an example for schemaid 746:
SQL> ed
Wrote file afiedt.buf
1 select bytes, segment_name, owner
2 from dba_segments
3 where (segment_name like '_746' or segment_name like '_746C%')
4* and segment_type = 'TABLE'
SQL> /
BYTES SEGMENT_NAME OWNER
---------- -------------- -------
3407872 B746 ARADMIN
2390491136 T746 ARADMIN
27525120 H746 ARADMIN
932446208 B746C701000357 ARADMIN
B746C701000357 is the lob segment for field id 701000357. The others
are the base tables. You can run the query without the where clause
for segment_type to get the indexes, lob segment indexes, etc:
select bytes, segment_name, owner
from dba_segments
where (segment_name like '_746%' or segment_name like '_746C%')
Axton Grams
The opinions, statements, and/or suggested courses of action expressed
in this E-mail do not necessarily reflect those of BMC Software, Inc.
My voluntary participation in this forum is not intended to convey a
role as a spokesperson, liaison or public relations representative for
BMC Software, Inc.
On Wed, Nov 18, 2009 at 12:57 AM, William Rentfrow
<wrentf...@stratacominc.com> wrote:
> **
>
> I've read a LOT of the documentation, etc, regarding how LOB storage works
> (including Axton's very fine wiki on this topic) and I'm pretty familiar
> with it in theory.
>
> We are doing a data migration at the moment which is using an enormous
> amount of space. We are in the process of migratiing 330,000 records as a
> test.
>
> This has used (so far) 33 GB of table space. We only have 47 GB in this
> test system. Considering the actual ARX exports were < 1 GB something is
> clearly not right here.
>
> Initial consultation with a DBA has indicated these rows in the T1114 (aka,
> HPD:HelpDesk) are LOB's:
>
> C1000005940 z1D Template Related CI
> C1000000151 Detailed Decription
> C1000000156 Resolution
> C1000003742 z1D Mobile Worklog Upd
> C300270900 Reason Description
>
> All of these are character fields set to 0 (unlimited) length.
>
> The questions I have are:
>
> 1.) If a LOB is > 4K it is stored out of row regardless. Can anyone tell me
> how to determine the space used by any of those particular fields in
> Oracle? And how I can tell if these are in fact being stored out of row due
> to the "unlimited" option on the field?
>
> 2.) Aren't z1D fields supposed to be Display only according to Remedy's
> design and naming conventions? I'm pretty sure that is the case but both of
> the fields above with that prefix are stored as optional.
>
> Thanks in advance -
>
> William Rentfrow
> Principal Consultant, StrataCom Inc.
> wrentf...@stratacominc.com
> O 715-592-5185
> C 715-410-8056
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
