| 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
