1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
CREATE OR REPLACE PACKAGE BODY B.PACKPRIVE AS
-- return 1 if the size is OK or auto extensible
-- return 0 if the size is about to be reached
FUNCTION checkSpace RETURN NUMBER AS
schemaSize NUMBER;
maxSize NUMBER;
margin NUMBER;
isExtensible VARCHAR2(10);
sql_stmt1 VARCHAR2(500);
curseur T_CURSOR;
BEGIN
-- set the margin to 100 MB.
margin:=100;
/*
sql_stmt1:='SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES '
|| 'FROM sys.dba_segments a,sys.dba_data_files b '
|| 'WHERE a.OWNER = ''B'' AND a.tablespace_name = b.tablespace_name '
|| 'GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES ';
SELECT SUM(a.BYTES) /1024 /1024 ,b.AUTOEXTENSIBLE, b.MAXBYTES
INTO schemaSize, isExtensible, maxSize
FROM sys.dba_segments a,sys.dba_data_files b
WHERE a.OWNER = 'B' AND a.tablespace_name = b.tablespace_name
GROUP BY a.OWNER,b.AUTOEXTENSIBLE,b.MAXBYTES;
*/
SELECT BYTES /1024 /1024 , MAX_BYTES
INTO schemaSize, maxSize
FROM sys.DBA_TS_QUOTAS
WHERE username='B';
--EXECUTE IMMEDIATE sql_stmt1;
--EXECUTE IMMEDIATE sql_stmt INTO schemaSize,isExtensible,maxSize ;
--INSERT INTO WEI.DEBUG values('Max: ' || maxSize || ' - schemaSize: ' || schemaSize || ' - isExt?: ' || isExtensible);commit;
IF(maxSize = -1 OR ((maxSize /1024 /1024)-margin) > schemaSize ) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
EXCEPTION
WHEN DEAD_LOCK THEN
RAISE_APPLICATION_ERROR(DL_ErrorCode, DL_ErrorMsg,TRUE);
WHEN NO_DATA_FOUND THEN --lors du SELECT
RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
WHEN DATE_MISMATCH THEN
RAISE_APPLICATION_ERROR(DM_ErrorCode,DM_ErrorMsg,TRUE);
WHEN FIELD_TOO_LARGE THEN
RAISE_APPLICATION_ERROR(FTL_ErrorCode,FTL_ErrorMsg,TRUE);
WHEN UPDATE_TO_NULL THEN
RAISE_APPLICATION_ERROR(FVR_ErrorCode,FVR_ErrorMsg,TRUE);
WHEN OTHERS THEN
RAISE;
END checkSpace;
END A.PACKPRIVE |
