How do I manually see my free and used tablespace in the Sentinel database?

  • 7000313
  • 02-May-2008
  • 26-Apr-2012

Environment

Novell Sentinel 6.0.xx Sentinel Database Manager
Novell Sentinel 6.0.xx Sentinel Server
Novell Sentinel 6.0.xx

Situation

Novell Sentinel uses a database to hold captured events and other data for extended durations. It is useful to have the ability to check the free and used space in each tablespace for planning purposes. The actual checks for free space can also be useful when Sentinel's specific components misreport the amount of free space because of calculation problems related to tablespaces that can autogrow as they are used.

Resolution

Finding the free and used space per tablespace is an exercise left up to the Database Administrator (DBA) over the database. The following queries have been found to work with Sentinel 6.0 using the Oracle database.

#Find the total space used in each Oracle tablespace.
SELECT tablespace_name, SUM(bytes)/1024 AS kilobytes FROM dba_segments GROUP BY tablespace_name

#Get the total tablespace from auto-growing datafiles in each Oracle tablespace.
SELECT tablespace_name, SUM(maxbytes)/1024 AS kilobytes FROM dba_data_files WHERE autoextensible = 'YES' GROUP BY tablespace_name

#Get the total tablespace from non-growing datafiles in each Oracle tablespace.
SELECT tablespace_name, SUM(bytes)/1024 AS kilobytes FROM dba_data_files WHERE autoextensible = 'NO' GROUP BY tablespace_name


The following queries work in Sentinel 6.0 with microsoft mssql server 2005:

#Get tablespace Used Space (kilobytes).
SELECT COALESCE( fg.groupname, 'LOG') AS TABLESPACE_NAME, CAST(SUM(( FILEPROPERTY( f.name, 'SpaceUsed')) * 8 ) AS numeric( 20, 2 ) ) as USED_SPACE FROM sysfiles AS f LEFT JOIN sysfilegroups AS fg ON fg.groupid = f.groupid GROUP BY fg.groupname

#Get static files' max space (kilobytes).
SELECT COALESCE( fg.groupname, 'LOG') AS TABLESPACE_NAME, CAST( ( SUM(CAST(f.size AS bigint)) * 8 ) AS numeric( 20, 2 ) ) as TOTAL_SPACE FROM sysfiles AS f LEFT JOIN sysfilegroups AS fg ON fg.groupid = f.groupid WHERE growth = 0 GROUP BY fg.groupname

#Get auto-growing files' max space (kilobytes).
SELECT COALESCE( fg.groupname, 'LOG') AS TABLESPACE_NAME, CAST( ( SUM(CAST(f.size AS bigint)) * 8 ) AS numeric( 20, 2 ) ) AS TOTAL_SPACE FROM sysfiles AS f LEFT JOIN sysfilegroups AS fg ON fg.groupid = f.groupid WHERE growth <> 0 GROUP BY fg.groupname

Additional Information

Sample Output:

SQL> SELECT tablespace_name, SUM(bytes)/1024 AS kilobytes FROM dba_segments GROUP BY tablespace_name;

TABLESPACE_NAME KILOBYTES
------------------------------ ----------
SENT_LOBS 1792
ESENTX2 171008
SYSAUX 326208
ESENTD2 175104
SENT_AUDITD 4096
SENT_AUDITX 1024
SENT_SMRYD 7063552
ESENTWFD 95232
ESENTWFX 164864
SYSTEM 501376
ESENTX 19041280

TABLESPACE_NAME KILOBYTES
------------------------------ ----------
SENT_ADVISORX 21504
SENT_ADVISORD 23552
ESENTD 13178880
SENT_SMRYX 3531776
UNDO 52864

16 rows selected.

SQL> SELECT tablespace_name, SUM(maxbytes)/1024 KILOBYTES FROM dba_data_files WHERE autoextensible = 'YES' GROUP BY tablespace_name;

TABLESPACE_NAME KILOBYTES
------------------------------ ----------
ESENTX2 2048000
SENT_LOBS 2048000
SYSAUX 2048000
ESENTD2 2048000
SENT_AUDITD 2048000
SENT_AUDITX 2048000
ESENTWFD 2048000
SENT_SMRYD 12288000
ESENTWFX 2048000
SYSTEM 2048000
SENT_ADVISORX 10241024

TABLESPACE_NAME KILOBYTES
------------------------------ ----------
ESENTX 20480000
SENT_ADVISORD 15361024
UNDO 2048000
ESENTD 45056000
SENT_SMRYX 8192000
16 rows selected.

SQL> SELECT tablespace_name, SUM(bytes)/1024 kilobytes FROM dba_data_files WHERE autoextensible = 'NO' GROUP BY tablespace_name;

TABLESPACE_NAME KILOBYTES
------------------------------ ----------
ESENTX 4096000

SQL>