| Index tablespace isnot growing [message #552546] |
Thu, 26 April 2012 08:05  |
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 #552565 is a reply to message #552551] |
Thu, 26 April 2012 09:52  |
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 |
cookiemonster
Messages: 13920
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 |
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 |
John Watson
Messages: 8929
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 |
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 #552577 is a reply to message #552573] |
Thu, 26 April 2012 10:58 |
John Watson
Messages: 8929
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
