In my previous articles i have given the basic idea about the converting seconds in to time format and viceversa. In this article i would like to give you multiple examples of how to calculate total size of the database in multiple databases. There are various types of ways and queries we require to use to calculate the total size of the database.
Total size of database : Oracle
In this section i would like you to give information about the total size of database for oracle database. You might know that in oracle we have data files,redo log files,control files and temporary files.The size of databasses is nothing but the total size of all the control files,temporary files,redo log files and data files.
We can calculate the total size of oracle database with using query. The query consist of sum,nvl with using some system tables as follows :
dba_data_files : This is used to take information for Data files of oracle database.
dba_temp_files : These files are used to fetch the total memory information for temporary files.
log_files : We require to use the system view named v_$log to get information about total memory consumed by log_files.
control file : We need to use the memory size for control files with using view named v$controlfile.
We are calculating the size of database in GB.
Following query is very useful to calculate the size of database
select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) “Database_Size_GB”
from
dual;
Total size of database : MySQL
In this section i would like to explain the information to calculate the total size of database : MySQL. The steps are quite different than oracle but in mysql database also we can calculate the total size with using only one query. Following are steps to calculate the total size of the mysql database
Step 1 : Log in to mysql database
Query :
mysql -u username -p
Step 2 :Following query is used to determine the exact size of the database.
SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Database size in MB” FROM information_schema.TABLES GROUP BY table_schema;
Step 3 : If user wants to calculate the database information schemawise kindly use following query
SELECT table_name AS "Table", ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size in MB" FROM information_schema.TABLES WHERE table_schema = "database_name" ORDER BY (data_length + index_length) DESC;
Here we require to give the table schema name so as to calculate the database size.
Total size of database : Postgresql
Unlike the database size of oracle and mysql we can calculate the total size of the postgresql database.The query is quite simple as compare to other databases.
Query :
SELECT pg_size_pretty( pg_database_size(‘database_name’) );
The database_name is nothing but the name of database for which we need to calculate size.
Total size of database : SQL Server
We can calculate the total size of database in SQL server using multiple ways.
1.Executing the stored procedure named sp_spaceused :
The following query will give information about database size
USE Amit_database; EXEC sp_spaceused; Output : database_name database_size unallocated spac Amit_database 3172.00 MB 2511.76 MB
2.Executing sp_database procedure
The second stored procedure is sp_databases stored procedure with using which we can get information about databases and its size.
EXEC sp_databases; Output :
DATABASE_NAME DATABASE_SIZE REMARKS ------------------ ------------- ------- Amit_db 9848 null Customer 56384 null Knowledge 45616 null Student 76384 null
3.Using system views
We can use the system view to fetch the information about size the database.
Query :
SELECT name, size, size * 8/1024 ‘Size_in_MB’, max_size FROM sys.master_files;
output :
name Size Size_in_MB ------------------ ------------- ------- Amit_db 9848 9848 Customer 56384 56384 Knowledge 45616 45616 Student 76384 76384
There is another view named sys.database_files which also will give information about size of database in SQL server.
USE Amit_database;
SELECT
name,
size,
size * 8/1024 'Size_in_MB',
max_size
FROM sys.database_files;
These are some most important queries in multiple databases which are used to calculate the size of database.