DB_BLOCK_SIZE

This parameter in the init.ora is the most important. This can be done only during creation time. If you have already created the Database you cannot change this value. You will have to re-create the Database with a different size.

show parameter DB_BLOCK_SIZE;
SELECT
 name, value, display_value, description
 FROM
 v$parameter
 WHERE
 name = lower('DB_BLOCK_SIZE');

 

How to check for LOGGING/NOLOGGING objects in the DB?

Two example methods of querying the database for this information:

select owner , table_name, index_name
from dba_indexes
where logging='NO';

select tablespace_name, logging
from dba_tablespaces
/

Source: LOGGING OR NOLOGGING THAT IS THE QUESTION By Francisco Munoz Alvarez

Database Size

dba_data_files –  means total size of the data file.

The data file size is total of dba_free_space and dba_segments:
dba_data_files = dba_free_space + dba_segments

dba_segments – means used size of the data file

dba_free_space – means free size of the data file

Size Check Queries:

To get total datafiles size, free and used space in percentage:

select t2.total "TOTAL SIZE",t1.free "FREE SPACE",
 (t1.free/t2.total)*100 "FREE (%)",
 (1-t1.free/t2.total)*100 "USED (%)"
from (select sum(bytes)/1024/1024 free from dba_free_space) t1 ,
 (select sum(bytes)/1024/1024 total from dba_Data_files) t2;

To get the size of all Data files:

select sum(bytes)/1024/1024 "Data files size in MB" from dba_data_files;

To get the size of all TEMP files:

select nvl(sum(bytes),0)/1024/1024 "Temp files size in MB" from dba_temp_files;

To get the size of the on-line redo-logs:

SELECT SUM(BYTES)/1024/1024 "Redo files size in MB" FROM SYS.V_$LOG;

Total Size and free size:

select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"
, round(free.p / 1024 / 1024) || ' MB' "Free space"
from (select bytes from v$datafile
 union all
 select bytes from v$tempfile
 union all
 select bytes from v$log) used
, (select sum(bytes) as p from dba_free_space) free
group by free.p;

Individual tablespace size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Size"
from dba_data_files
 group by tablespace_name
 ORDER BY 1;

Individual tablespace used size:

select tablespace_name as "Tablespace Name",sum(bytes)/(1024*1024) as "Used Size"
from dba_segments
 GROUP BY TABLESPACE_NAME
 order by 1;

Individual tablespace free space:

select tablespace_name as "Tablespace Name", sum(bytes)/(1024*1024) as "Free Space"
 from dba_free_space
 group by tablespace_name
 order by 1;

Reference Links:

Sample MySQL Configuration File and Parameter Details

Here is a copy of a sample MySQL configuration file and details on few main parameters:

# Example mysql config file for small systems.
#
# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it's important that the mysqld deamon
# doesn't use much resources.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password	= your_password
port		= 3306
socket		= /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port		= 3306
socket		= /tmp/mysql.sock
skip-locking
set-variable	= key_buffer=128K
set-variable	= max_allowed_packet=1M
set-variable	= thread_stack=64K
set-variable	= table_cache=4
set-variable	= sort_buffer=64K
set-variable	= net_buffer_length=2K
server-id	= 1

# Uncomment the following if you want to log updates
#log-bin

# Uncomment the following if you are NOT using BDB tables
#skip-bdb

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:100M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable	= max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable	= key_buffer=8M
set-variable	= sort_buffer=8M

[myisamchk]
set-variable	= key_buffer=8M
set-variable	= sort_buffer=8M

Source

 

Config file parameters:

 

Reference Links: