Alert on Table size

oracleoracle-11g-r2size;table

I need to create an alert on my application table when it reaches 98% of its threshold. I wrote an SP which checks the rows and logs the information into a log file which was monitored by alert system and that raised alarms successfully.

But now my DBA team is saying they cannot grant the application DB user read/write privileges on UTL_FILE in production. And OEM (Oracle Enterprise Manager) can't be used at application level.

What are my options?
How can I get an alert on table size?

Reference: UTL_FILE

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

Best Answer

You have not explained, how you start your SP. If done via db-jobs, then you might have this problem.

But you can also start a request via any client-tool. Simply by using sqlplus or any other client, connecting the db and returning the threshold to any location you like.

Similar way (client-access) is made by many monitoring tools. You can create a separate account with very limited and restricted access. You can even de-couple the read access by a special higher privileged SP, that returns just the threshold without having SELECT privilege on monitored table itself.

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

When you delete from heaps, allocated space may not be unallocated unless you use a table lock. See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew] 
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...

Sql-server – Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

Check that if you have indexes (clustered/nonclustered, full text). Use sp_spaceused 'your_table_name' to check that you can actually discard indexes.
Check what type of table is used. In your example of 0.2KB=205 Bytes you will have 38 rows per data page if your table is heap and 39 rows per data page if it is clustered table.

Please see below the example:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT --IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Heap. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO


IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 39

-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
SELECT 'This is Clustered Index. Note Data Space'
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

Check for random inserts/updates/deletes in your table. This may be an issue as free space is not reclaimed back. The free space may be wasted during page splits too.

Please see below the example with average row size 205 Bytes and 200 rows (just like in your case). Table data size is 1.57 MB:

IF EXISTS (SELECT * FROM sys.tables
            WHERE name = 'sparse_pages')
    DROP TABLE sparse_pages;
GO
CREATE TABLE sparse_pages 
(
KeyField SMALLINT IDENTITY (1,1) PRIMARY KEY
, Filler VARCHAR(8000) null 
)
GO

Enter the data

SET NOCOUNT ON
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 8700))
INSERT INTO sparse_pages( Filler) values ( REPLICATE('a', 192))
GO 200

DELETE FROM sparse_pages 
WHERE LEN(Filler)>300
GO
-- Average row size now 205 Bytes
WAITFOR DELAY '00:00:03';
GO
-- Check table size
sp_spaceused 'sparse_pages'
GO

Best Answer

Related Solutions

Sql-server – SQL Server Table Size Discrepancy

Sql-server – Adding around 200 rows in a table grows size of the table by 400kb where avg row size is 0.2KB

Related Question