Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL and System Tablespace
PL/SQL and System Tablespace [message #191154] Tue, 05 September 2006 01:24 Go to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
Hi

There’s a PL/SQL package in a user schema which is called by a Java program. With all the data and this package the system tablespace is about 500MB. But every time this package is executed concurrently (using multiple instance of the same java program) the size of the system tablespace increases. There are no other packages or SQL running in the DB at the time its execution. What’s the relation between stored procedures and increase of the system tablespace? I read that definitions of the stored procedures are kept in the system tablespace. I assume that would cause the system tablespace to increase in size initially. But this happens every time the package is executed concurrently.
Why is this happening?
Is there a way around this?

Thank you.
Re: PL/SQL and System Tablespace [message #191255 is a reply to message #191154] Tue, 05 September 2006 07:32 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

Are You 100% sure that it is the source code expanding the system tablespace?

Fx, could it be a TEMP segment, placed in the SYSTEM tablespace, hypothetically speaking.

If You create a table with a snapshot of dba_segments, You could verify that no objects infact did increase in size.

Br
Kim
Re: PL/SQL and System Tablespace [message #191266 is a reply to message #191255] Tue, 05 September 2006 07:44 Go to previous messageGo to next message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member
Hi,

i wouldn't say the source code it's something that happens when the package get executed.

Looking at the content of the system tablespace I’ve come across a lobsegment called SYS_LOB0000000564C00013$$ which seems to extend every time this PL/SQL package is executed. Size of it is over 1G and growing. This lobsegment is in the FGA_LOG$ table. quering obj$schema and obj$name columns of this table showed names of 3 tables from the user schema. i'm not sure what the connection is but truncating (it was a test database so i could do anything without worrying too much) fga_log$ table kind of made that large logsegment disappear and reduce the tablespace size. (to the value of the minimum extent possible.)

that's where i stand now. so any further info and explanation is welcome.

thank you.
Re: PL/SQL and System Tablespace [message #191278 is a reply to message #191266] Tue, 05 September 2006 08:02 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Hi

It would seem that You have enabled FGA (Fine Grained Audit), and that You are seeing the log expand as You are using the database.

This I think is expected behaviour.

Perhaps You should disable FGA?

Br
Kim
Re: PL/SQL and System Tablespace [message #191281 is a reply to message #191278] Tue, 05 September 2006 08:08 Go to previous message
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
Senior Member

Hi,
I didn't enable FGA unless it's enable by default.

How to disable this? your help is most welcome!!


thanks
Previous Topic: How to sum values on individual records?
Next Topic: Cursor rows to be inserted into table columns
Goto Forum:
  


Current Time: Sun Dec 04 04:49:41 CST 2016

Total time taken to generate the page: 0.06882 seconds