Home » RDBMS Server » Server Administration » Why din I see warning threshold in my alert log (oracle 10gr2)
Why din I see warning threshold in my alert log [message #386211] Fri, 13 February 2009 02:44
kytemanaic
Messages: 55
Registered: February 2009
Member
this is how I create my tablespace

create tablespace NEWBIEDATA
datafile 'C:\oracle\oradata\tomkyte\newbie.dbf' size 2M
logging
extent management local uniform size 40k
segment space management auto
flashback on;


Always use this editor
Always use this editor
Always use this editor

* Rich Text
* Plain Text
* Preview

Please Wait ...

Click for bold Click for italics Click for underline Click to insert a link Spell Check
Please enter a URL.

URL -- example: http://mysite.com/ Link Text(optional) -- example: My Site

this is how I create my tablespace

create tablespace NEWBIEDATA
datafile 'C:\oracle\oradata\tomkyte\newbie.dbf' size 2M
logging
extent management local uniform size 40k
segment space management auto
flashback on;

this is one I create my user

create user NEWBIE identified by n3wbi3321
default tablespace NEWBIEDATA
temporary tablespace temp
quota 1300K on NEWBIEDATA;

this is how I create my role

create role NEWBIEROLE identified by n3wbi3321;

grant create table to NEWBIEROLE;

grant NEWBIEROLE to NEWBIE;
grant CONNECT to NEWBIE;

this is how I set my threshold

begin

DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id =>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator =>DBMS_SERVER_ALERT.OPERATOR_GE,
warning_value =>'60',
critical_operator =>DBMS_SERVER_ALERT.OPERATOR_GE,
critical_value =>'75',
observation_period =>1,
consecutive_occurrences =>1,
instance_name =>'TOMKYTE',
object_type =>DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name =>'NEWBIEDATA');

end;
/


login as newbie

create tablespace NEWBIEDATA
datafile 'C:\oracle\oradata\tomkyte\newbie.dbf' size 2M
logging
extent management local uniform size 40k
segment space management auto
flashback on;

declare
v_number number;
begin
begin
select max(NEWBIEID) into v_number from NEWBIETABLE;
end;

while 1=1 loop
v_number := v_number +1;
insert into NEWBIETABLE (NEWBIEID, NEWBIENAME) values (v_number, v_number );
commit;
end loop;
end;
/

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'NEWBIEDATA'
ORA-06512: at line 10

login as sysdba

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

here's the result

Tablespace Name Free Size(MB) Used Size(MB) Total Size(MB)
------------- --------------
EXAMPLE 22.625 77.375 100
NEWBIEDATA .6640625 1.3359375 2
SYSAUX 7.5 252.5 260
SYSTEM 2 478 480
UNDOTBS1 10.5625 19.4375 30
USERS 1.75 3.25 5

next i determine the background dump

Tablespace Name Free Size(MB) Used Size(MB) Total Size(MB)
------------- --------------
EXAMPLE 22.625 77.375 100
NEWBIEDATA .6640625 1.3359375 2
SYSAUX 7.5 252.5 260
SYSTEM 2 478 480
UNDOTBS1 10.5625 19.4375 30
USERS 1.75 3.25 5

used size of NEWBIEDATA is 1.3359375 which should has reach warning level, why there is no warning indication for NEWBIEDATA reaching the warning threshold which is 60%?

thanks

-
ogin as newbie

create tablespace NEWBIEDATA
datafile 'C:\oracle\oradata\tomkyte\newbie.dbf' size 2M
logging
extent management local uniform size 40k
segment space management auto
flashback on;

declare
v_number number;
begin
begin
select max(NEWBIEID) into v_number from NEWBIETABLE;
end;

while 1=1 loop
v_number := v_number +1;
insert into NEWBIETABLE (NEWBIEID, NEWBIENAME) values (v_number, v_number );
commit;
end loop;
end;
/

ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'NEWBIEDATA'
ORA-06512: at line 10

login as sysdba

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

here's the result

Tablespace Name Free Size(MB) Used Size(MB) Total Size(MB)

NEWBIEDATA .6610625 1.3359375 2

Why is there no warning level in the alert log?

thanks
Previous Topic: DB Link
Next Topic: rename datafile needs media Recovery
Goto Forum:
  


Current Time: Tue Dec 06 10:45:49 CST 2016

Total time taken to generate the page: 0.08609 seconds