Home » SQL & PL/SQL » SQL & PL/SQL » LOBSEGMENT (Oracle 10g)
LOBSEGMENT [message #357666] Thu, 06 November 2008 03:38 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,

In my TEST tablespace one LOGSEGMENT size is growing very fastly how can check which SQL is using LOBSEGMENT?

Thanks,
Re: LOBSEGMENT [message #357674 is a reply to message #357666] Thu, 06 November 2008 03:55 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
My lobsegment name is SYS_LOB0000066027C00003$$.

How can check that which tables column is this lobsegnement?

Thanks,
Re: LOBSEGMENT [message #357692 is a reply to message #357674] Thu, 06 November 2008 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user_lobs.

Regards
Michel
Re: LOBSEGMENT [message #357711 is a reply to message #357666] Thu, 06 November 2008 05:53 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks,

I got the below output.
SELECT * FROM DBA_LOBS
WHERE tablespace_name='TEST'
AND segment_name ='SYS_LOB000066027C00003$$'

Table_name ColumnName Segment_name              Tablespace_name
-------------------------------------------------------------
TMPCLOB    DATA       SYS_LOB0000066027C00003$$ TEST

Now when i execute following query

SELECT *  FROM v$sql

I got the following output.

INSERT INTO TMP(INFO, DATA) VALUES('mail.Mail1: ' || :B3 || ' ' || :B2 , :B1 )

Please tell me how i can find from where the Insert Query is running?

So that i can check and kill that session if necessory.



Re: LOBSEGMENT [message #357715 is a reply to message #357711] Thu, 06 November 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Join v$sql with v$session on sql_id.

Regards
Michel

[Updated on: Thu, 06 November 2008 05:58]

Report message to a moderator

Re: LOBSEGMENT [message #357718 is a reply to message #357666] Thu, 06 November 2008 06:12 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Please tell me on which bases i should join.
WHERE v$session.sql_hash_value = v$sql.hash_value

Doesn't give me proper result
Thanks,
Re: LOBSEGMENT [message #357721 is a reply to message #357718] Thu, 06 November 2008 06:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
on sql_id.

Regards
Michel
Re: LOBSEGMENT [message #357724 is a reply to message #357666] Thu, 06 November 2008 06:34 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks michel,
Please help me more,
For this query,
SELECT *  FROM v$sql

I got the following output.

INSERT INTO TMP(INFO, DATA) VALUES('mail.Mail1: ' || :B3 || ' ' || :B2 , :B1 )



But for this query
SELECT *FROM v$session, v$sql
WHERE --v$sql.sql_id= 'gu6mj8w6atvx5' 
v$session.sql_id = v$sql.sql_id

It doesnt show any record related to,

SQLFULL_TEXT
-----------
INSERT INTO TMP(INFO, DATA) VALUES('mail.Mail1: ' || :B3 || ' ' || :B2 , :B1 )
 


IS it possibility that job is doing that which is running at backend?

Thanks,

[Updated on: Thu, 06 November 2008 06:37]

Report message to a moderator

Re: LOBSEGMENT [message #357728 is a reply to message #357724] Thu, 06 November 2008 06:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is likely that the sql is not currently being executed when you execute the query (much likely as a insert values is very short).
Try with v$session.prev_sql_id

Regards
Michel
Re: LOBSEGMENT [message #357739 is a reply to message #357666] Thu, 06 November 2008 07:21 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Michel,

In the table creatin script i can see the DATA as the CLOB object,
But it is not refereneced to any LOBSEGMENT,

CREATE TABLE TMP
(
  ID    INTEGER                                 NOT NULL,
  INFO  VARCHAR2(64 BYTE),
  DATA  CLOB
)
TABLESPACE TEST
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          31192K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING
ENABLE ROW MOVEMENT;


Then how can i see the Tables DATA Column associated with the LOBSEGMENT,

SELECT * FROM DBA_LOBS
WHERE tablespace_name='TEST'
AND segment_name ='SYS_LOB0000066027C00003$$'

Table_name ColumnName Segment_name              Tablespace_name
-------------------------------------------------------------
TMPCLOB    DATA       SYS_LOB0000066027C00003$$ TEST



Thanks,

[Updated on: Thu, 06 November 2008 07:23]

Report message to a moderator

Re: LOBSEGMENT [message #357740 is a reply to message #357739] Thu, 06 November 2008 07:29 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Because LOBS are not stored inline with the rest of the row, they are stored seperately. If yuo don't specify exactly where you want them to go, Oracle will put them somewhere for you.
Re: LOBSEGMENT [message #357874 is a reply to message #357666] Fri, 07 November 2008 00:23 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Very informative reply, Thanks,

Then if i will delete records from the base table then will it release space from the LOBSEGMENT?

Thanks,
Re: LOBSEGMENT [message #357881 is a reply to message #357874] Fri, 07 November 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The space is freed inside in the log segment but it does not return to the tablespace, it is available for new lob data.

Regards
Michel
Re: LOBSEGMENT [message #357886 is a reply to message #357666] Fri, 07 November 2008 00:52 Go to previous message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Can we use below statement to release space from LOBSEGMNET which is related to TEST(DATA) column
ALTER TABLE TEST SHRINK SPACE;


Thanks,
Previous Topic: Validating data
Next Topic: buffer overflow
Goto Forum:
  


Current Time: Thu Dec 08 20:11:53 CST 2016

Total time taken to generate the page: 0.24445 seconds