Home » RDBMS Server » Server Administration » Index tablespace isnot growing (oracle 10g 64 bit, and RHEL 5.3)
Index tablespace isnot growing [message #552546] Thu, 26 April 2012 08:05 Go to next message
morad_dba
Messages: 93
Registered: June 2008
Member
Dear all,

In our production database server Index tablespace is not growing now... On average index tablespace increases 200 MB to 250 MB per day. Last two weeks index tablespace does not increase..

I am giving you all a statistics of our production db Tablespace usage..

Date Globusdataxml(MB) Globusindexxml(MB)
23-4-2012 26321025 1581.94
24-4-2012 24633.31 1584.25
25-4-2012 23410.88 1576.25
26-4-2012 22186.19 1573.25

Please tell me what to do?

Regards,
Morad.

[Updated on: Thu, 26 April 2012 08:15]

Report message to a moderator

Re: Index tablespace isnot growing [message #552551 is a reply to message #552546] Thu, 26 April 2012 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why do you think the fact that the tablespace isn't growing is a problem?
Re: Index tablespace isnot growing [message #552552 is a reply to message #552551] Thu, 26 April 2012 08:23 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

previously was any archive or data cleanup task completed?
Re: Index tablespace isnot growing [message #552565 is a reply to message #552551] Thu, 26 April 2012 09:52 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member

The database is our Banking production Database. Normal event is data tablespace increases 1 GB to 1.5 GB and index tablespace increases 200 MB to 250 MB every day.

Now why index tablespace does not increase. That means our daily transaction data does not uses index. ( New transaction data does not have index leaf entry ?

Regards,
Morad.

Re: Index tablespace isnot growing [message #552567 is a reply to message #552565] Thu, 26 April 2012 10:07 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Presumably you can't expect it to keep growing for ever - you're bound to run out of disk space eventually.
So presumably there is a process to purge/archive old data.
Has it been run recently?
Re: Index tablespace isnot growing [message #552571 is a reply to message #552567] Thu, 26 April 2012 10:30 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member

We know the normal behavior is oracle database is when a new transaction is occurred in database then the new data must be indexed.

We don't have any option or scope to purge old data.. In banking database we have to keep all transaction from the beginning to today. Once in every 6 months we rebuild index then index tablespace decreases and over the time index tablespace again increases...

But last two weeks there is no increment in index tablespace..

Regards,

Morad.
Re: Index tablespace isnot growing [message #552572 is a reply to message #552546] Thu, 26 April 2012 10:33 Go to previous messageGo to next message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
Morad, you figures don't add up. This is your table of sizes, formatted properly:
Date      Globusdataxml(MB) Globusindexxml(MB)
23-4-2012 26321025          1581.94
24-4-2012 24633.31          1584.25
25-4-2012 23410.88          1576.25
26-4-2012 22186.19          1573.25

The first figure is missing a decimal point? Then it looks as though your data tablespace is shrinking, not growing. The index tablespace gets bigger, then it shrinks. This really isn;t possible. And your growth rates don't work either, unless the application has been running for only two or three weeks.

Are you certain that your figures are correct? From where do they come?
Re: Index tablespace isnot growing [message #552573 is a reply to message #552572] Thu, 26 April 2012 10:43 Go to previous messageGo to next message
morad_dba
Messages: 93
Registered: June 2008
Member

The tablespace space status info i posted is the free space in tablespace not the total space..

I have run the query below:

SQL> select tablespace_name, round(sum(bytes/1024/1024))
2 from dba_free_space group by tablespace_name;

TABLESPACE_NAME ROUND(SUM(BYTES/1024/1024))
------------------------------ ---------------------------
SYSTEM 63038
USERS 1015
GLOBUSINDEXXML 1575
GLOBUSDATAXML 21766
SYSAUX 593
UNDOTBS1 30720

6 rows selected.


Index tablespace size does not reduce now for last two weeks..

Normally index tablespace free space reduce 200 MB daily.. That means index tablespace size increases 200 MB Daily.

And data tablespace free space size reduce 1 GB to 1.5 GB daily That means data tablespace size increase 1 GB to 1.5 GB..


So now why our database index tablespace size does not grow every day.. That means new transaction info does not have index leaf entry?

Regards,
morad.
Re: Index tablespace isnot growing [message #552575 is a reply to message #552573] Thu, 26 April 2012 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 26763
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Index tablespace isnot growing [message #552577 is a reply to message #552573] Thu, 26 April 2012 10:58 Go to previous message
John Watson
Messages: 8531
Registered: January 2010
Location: Global Village
Senior Member
63G of free space in system? I would think some very strange things have been going in in your database. My approach would be to tighten up security: change the passwords on ALL accounts, lock all accounts that can be be locked (specially SYSTEM), revoke DBA from everyone, revoke as many other privileges as possible, enable auditing of all DDL and of CREATE SESSION and of all ANY privileges, make sure no-one uses generic logons.

And in future use [code] tags to format your posts, and don't say that a tablespace increases when what you mean is that free space within a tablespace decreases.
Previous Topic: Oracle Normal DB and APPLICATION DB in a single machine
Next Topic: Meaning of Audit_trail
Goto Forum:
  


Current Time: Wed Apr 14 17:24:17 CDT 2021