How to Retrieve ORACLE Usage Statistics

As promised before, I'm going to tell you how to retrieve ORACLE usage statistics. Just as MySQL, ORACLE also stores its statistics in some internal databases.Here is an SQL script to retrieve its disk usage statistics.

NOTE: Make sure you replace ":owner" with admin username.

	select * from (SELECT sum(total_table_meg)over (partition by owner) as DISK_USAGE, realsize FROM (
	SELECT
	owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,bytes/1024/1024 as realSize,
	tablespace_name, extents, initial_extent,
	Sum(bytes/1024/1024) OVER (PARTITION BY table_name) AS total_table_meg
	FROM (
	-- Tables
	SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,
	segment_name AS table_name, bytes,
	tablespace_name, extents, initial_extent
	FROM dba_segments
	WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
	UNION ALL
	-- Indexes
	SELECT i.owner, i.index_name AS object_name, 'INDEX' AS object_type,
	i.table_name, s.bytes,
	s.tablespace_name, s.extents, s.initial_extent
	FROM dba_indexes i, dba_segments s
	WHERE s.segment_name = i.index_name
	AND s.owner = i.owner
	AND s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
	-- LOB Segments
	UNION ALL
	SELECT l.owner, l.column_name AS object_name, 'LOB_COLUMN' AS object_type,
	l.table_name, s.bytes,
	s.tablespace_name, s.extents, s.initial_extent
	FROM dba_lobs l, dba_segments s
	WHERE s.segment_name = l.segment_name
	AND s.owner = l.owner
	AND s.segment_type = 'LOBSEGMENT'
	-- LOB Indexes
	UNION ALL
	SELECT l.owner, l.column_name AS object_name, 'LOB_INDEX' AS object_type,
	l.table_name, s.bytes,
	s.tablespace_name, s.extents, s.initial_extent
	FROM dba_lobs l, dba_segments s
	WHERE s.segment_name = l.index_name
	AND s.owner = l.owner
	AND s.segment_type = 'LOBINDEX'
	)
	WHERE owner = UPPER(':owner')
	)
	--AND total_table_meg > 10
	ORDER BY total_table_meg DESC, meg DESC
	)
	where ROWNUM <=2

view raw oracle_disk_usage.sql hosted with ❤ by GitHub

Reference: http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle

Next post will be on how to retrieve statistics data in Apache Cassandra.

How to Retrieve MySQL Usage Statistics

In enterprise applications, monitoring usage statistics of database servers it uses is useful in many ways. It can be a situation where you need to meter and bill usage of system, or just to keep track of how data grows and system works. 

I'm trying to make you aware of different ways of statistic retrieval methods of some popular DBMS systems. As the first step, in this post I'm going to talk about how to retrieve MySQL usage statistics.

MySQL itself stores statistics data and metadata in some of its internal databases, namely mysql, information_schema and performance_schema. We can access those databases to get what we need. MySQL also gives some special queries for that.

Let's start with a simple one. This is how you can retrieve current session statistics.

show status;

view raw show_status.sql hosted with ❤ by GitHub

And this is how you can see global statistics.

show global status;

view raw show_global_status.sql hosted with ❤ by GitHub

This is one way you can retrieve disk usage statistics for each database.

	SELECT
	table_schema "DATABASE_NAME",
	(sum( data_length + index_length ) / 1024 / 1024) "DISK_USAGE",
	(sum(data_free)/ 1024 / 1024) "FREE_SPACE"
	FROM information_schema.TABLES
	GROUP BY table_schema;

view raw per_db_stat_sql.sql hosted with ❤ by GitHub