| how can i know the oracle database size [message #58258] |
Mon, 11 August 2003 01:54  |
Rahman
Messages: 40
Registered: July 2003
|
Member |
|
|
hi friend!
i am using oracle 8i
how can i know the oracle database size already exists in the server & what is the way of increase the database size,
& one other question
i have a table MPI with Primary Key MRN & among the other columns CiviId is defined as Unique Key, now whwn i search record using CivilId (where clause) it's taking much time to search a record(from 300000 records are in table), how to speed up the searching, is it better to create a index on CivilId column to speedup, please help me
any suggestion will be highly appreciated
rahman
|
|
|
|
| Re: how can i know the oracle database size [message #58310 is a reply to message #58258] |
Wed, 13 August 2003 12:39  |
Satish Shrikhande
Messages: 167
Registered: October 2001
|
Senior Member |
|
|
Database Size -
select avail.tablespace_name, round(avail.available_mb) available_mb,
round(used.used_mb) used_mb,
round(nvl(used.used_mb,0)/nvl(avail.available_mb,1)*100) pct_used
from
(select substr(tablespace_name,1,40) tablespace_name,
sum(bytes/(1024*1024)) available_mb
from dba_data_files
group by substr(tablespace_name,1,40)) avail,
(select substr(tablespace_name,1,40) tablespace_name,
sum(bytes/(1024*1024)) used_mb
from dba_extents
group by substr(tablespace_name,1,40)) used
where avail.tablespace_name = used.tablespace_name
It would be better to have a index key on that column .
|
|
|
|
| Re: how can i know the oracle database size [message #58342 is a reply to message #58310] |
Sat, 16 August 2003 02:51 |
Rahman
Messages: 40
Registered: July 2003
|
Member |
|
|
Hi Satish Shrikhande,
Thanks a lot for ur help
Ur Code display the following
TABLESPACE_NAME AVAILABLE_MB USED_MB PCT_USED
------------------------------ ------------ --------- ---------
RBS 150 12 8
SYSTEM 140 135 96
USERS 268 211 79
so, total of available_mb will be the size of Database
plz consider my other Q. is :-
this is new Database & heavy transaction is going on day by day so how can i know that BataBase size will automatically increase or not?
But few days ago when huge amount of rows were added due to the size error was dispayed the i added a User DataFile of 300MB after that it's working fine.
Addition of BIG size 300MB DataFile is wrong or right?
thanks in advance
rahman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
