Hi,
I am very new to Oracle DBA activity. We had a issue with Oracle database and was trying to find out the root cause. The following error I found in alert_SID.log, due to this All transaction associated with that Tablespace got hung.
Mon May 21 13:24:21 2007
ORA-1654: unable to extend index SAPR3.M_ZSAR____0 by 7500 in tablespace PSAPPOOLI
Mon May 21 14:35:31 2007
I am using this query to findout the space %free for all Tablespace on daily basis.
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
And for PSAPPOOLI, I am getting
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
PSAPPOOLI 11337 89 11426 1
89 MB free it is showing for PSAPPOOLI Tablespace. And the same figure I was getting more than a month. How can I solve this problem? Do I need to change the maxm. extent to higher value? How can I do that? It is production database. So want to confirm this before doing any activity.
And also I want a UNIX script, which will check the error periodically in alert_SID.log and will notify me for such alert.
Any help on this issue will be highly appreciated.
Thanks agian.
Regards,
-Sam
NB: I am using following query to find the free space.
select tablespace_name, sum(bytes)/1024/1024 MB from dba_free_space
where TABLESPACE_NAME='PSAPPOOLI' group by TABLESPACE_NAME;
I am very new to Oracle DBA activity. We had a issue with Oracle database and was trying to find out the root cause. The following error I found in alert_SID.log, due to this All transaction associated with that Tablespace got hung.
Mon May 21 13:24:21 2007
ORA-1654: unable to extend index SAPR3.M_ZSAR____0 by 7500 in tablespace PSAPPOOLI
Mon May 21 14:35:31 2007
I am using this query to findout the space %free for all Tablespace on daily basis.
select
fs.tablespace_name "Tablespace",
(df.totalspace - fs.freespace) "Used MB",
fs.freespace "Free MB",
df.totalspace "Total MB",
round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
from
(select
tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from
dba_data_files
group by
tablespace_name
) df,
(select
tablespace_name,
round(sum(bytes) / 1048576) FreeSpace
from
dba_free_space
group by
tablespace_name
) fs
where
df.tablespace_name = fs.tablespace_name;
And for PSAPPOOLI, I am getting
Tablespace Used MB Free MB Total MB Pct. Free
------------------------------ ---------- ---------- ---------- ----------
PSAPPOOLI 11337 89 11426 1
89 MB free it is showing for PSAPPOOLI Tablespace. And the same figure I was getting more than a month. How can I solve this problem? Do I need to change the maxm. extent to higher value? How can I do that? It is production database. So want to confirm this before doing any activity.
And also I want a UNIX script, which will check the error periodically in alert_SID.log and will notify me for such alert.
Any help on this issue will be highly appreciated.
Thanks agian.
Regards,
-Sam
NB: I am using following query to find the free space.
select tablespace_name, sum(bytes)/1024/1024 MB from dba_free_space
where TABLESPACE_NAME='PSAPPOOLI' group by TABLESPACE_NAME;