How Much Space an Index is Using

In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:

analyze index validate structure;

Analyze index validate structure checks the structure of the index and
populates a table called index_stats. The command (in this form) does not
gather statistics for use by the Cost Based Optimizer. The index_stats table
can only hold 1 row at a time. So, if you are planning to store index data for
multiple indexes or for historical comparison purposes, you will need to
insert the data into a more permanent table. Statistics are also lost at the
end of each session.

This analyze command only checks the structure of the index and populates the
index_stats table. It does not gather statistics for use by the Cost Based
Optimizer.

Once you have gathered the statistics, they can be retrieved as follows:

column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

Example output
==============

SQL> analyze index draw1 validate structure;

Index analyzed.

SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
DRAW1 433 426 6 1

It is also possible to gather statistics on the use of space within the
btree itself:

select name,
btree_space,
used_space,
pct_used
from index_stats;

Example output
==============

SQL> select name, btree_space, used_space, pct_used
2 from index_stats;

NAME BTREE_SPACE USED_SPACE PCT_USED
--------------- ----------- ---------- ----------
DRAW1 810624 236284 30

Using DBA_FREE_SPACE

Oracle Metalink 121259.1

View DBA_FREE_SPACE can be used to determine the space available in the database.

Sometimes the DBA does not know why Oracle is unable to allocate a new extent and for a quick solution he proceeds to add a new datafile.  Not always is this solution is the best.  Oracle has some views that help to determine which solution is better.  These views are:

DBA_FREE_SPACE:  lists the free extents in all tablespaces

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the extent
FILE_ID	NUMBER	NOT NULL	ID number of the file containing the extent
BLOCK_ID	NUMBER	NOT NULL	Starting block number of the extent
BYTES	NUMBER		Size of the extent in bytes
BLOCKS	NUMBER	NOT NULL	Size of the extent in Oracle blocks
RELATIVE_FNO	NUMBER	NOT NULL	Relative file number of the first extent block

DBA_FREE_SPACE_COALESCED: This view help to determine how coalesce is a tablespace.

Column	Datatype	NULL	Description
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of tablespace
TOTAL_EXTENTS	NUMBER		Total number of free extents in tablespace
EXTENTS_COALESCED	NUMBER		Total number of coalesced free extents in tablespace
PERCENT_EXTENTS_COALESCED	NUMBER		Percentage of coalesced free extents in tablespace
TOTAL_BYTES	NUMBER		Total number of free bytes in tablespace
BYTES_COALESCED	NUMBER		Total number of coalesced free bytes in tablespace
TOTAL_BLOCKS	NUMBER		Total number of free Oracle blocks in tablespace
BLOCKS_COALESCED	NUMBER		Total number of coalesced free Oracle blocks in tablespace
PERCENT_BLOCKS_COALESCED	NUMBER		Percentage of coalesced free Oracle blocks in tablespace

Errors such as:

ORA-1653 unable to extend table by in tablespace

ORA-1654 unable to extend index by for tablespace

ORA-1655 unable to extend cluster e by for tablespace

ORA-1658 unable to create INITIAL extent for segment in tablespace %s

ORA-1659 unable to allocate MINEXTENTS beyond in tablespace

indicate to DBA there is not free extent in the tablespace reported to support the new extent.  Consulting the view DBA_FREE_SPACE the DBA can know if really the tablespace does not have space available or the tablespace is fragmented and reorganization should be made.  Remember that two contiguous extents are considered two free spaces and both spaces can not be neither summarized nor counted as one for free space contiguous.

A simple query to get information about the free space in a tablespace is:

SELECT TABLESPACE_NAME, BLOCK_ID, BLOCKS, BYTES FROM DBA_FREE_SPACE;

Depending on the problem, the solution to apply can vary from adding new datafile to recreating the tablespace in order to defragment it.  To know which is the problem, the DBA should look up on DBA_FREE_SPACE and check how freed is the tablespace.

If the tablespace have no space avaliable, the solution can be:

1.  Add a new datafile using ALTER TABLESPACE command.

2.  Resize the current datafile using ALTER DATABASE command.

If the tablespace is fragmented the only solution is to reorganize the tablespace.

Some useful scripts:

--SPACE AVAILABLE IN TABLESPACES          select a.tablespace_name,        round(sum(a.tots)/1024/1024) Tot_Size_MB,          round(sum(a.sumb)/1024/1024) Tot_Free_MB,         round(sum(a.sumb)*100/sum(a.tots)) Pct_Free,          round(sum(a.largest)/1024/1024) Max_Free_MB,        sum(a.chunks) Chunks_Free   from   (   select tablespace_name, 0 tots, sum(bytes) sumb,           max(bytes) largest, count(*) chunks   from   DBA_FREE_SPACE a   group by tablespace_name   union   select tablespace_name, sum(bytes) tots, 0, 0, 0  from   DBA_DATA_FILES    group by tablespace_name) a   group by a.tablespace_name;

--SEGMENTS WITH MORE THAN 20 EXTENTS          select owner, segment_name, extents,        round(bytes/1024/1024) size_MB,          max_extents, next_extent   from   DBA_SEGMENTS    where  segment_type in ('TABLE','INDEX') and extents > 20   order by owner, segment_name;

Last updated: 2009-11-30 Monday

Oracle initializatoin parameters

Note: Oracle 11g R1

PARAMETER NAME	DESCRIPTION
O7_DICTIONARY_ACCESSIBILITY	Version 7 Dictionary Accessibility Support
active_instance_count	number of active instances in the cluster database
aq_tm_processes	number of AQ Time Managers to start
archive_lag_target	Maximum number of seconds of redos the standby could lose
asm_diskgroups	disk groups to mount automatically
asm_diskstring	disk set locations for discovery
asm_power_limit	number of processes for disk rebalancing
asm_preferred_read_failure_groups	preferred read failure groups
audit_file_dest	Directory in which auditing files are to reside
audit_sys_operations	enable sys auditing
audit_syslog_level	Syslog facility and level
audit_trail	enable system auditing Oracle parameter audit_trail and "alter database open read only" Notes on Auditing in Oracle Database
background_core_dump	Core Size for Background Processes
background_dump_dest	Detached process dump directory
backup_tape_io_slaves	BACKUP Tape I/O slaves
bitmap_merge_area_size	maximum memory allow for BITMAP MERGE
blank_trimming	blank trimming semantics parameter
buffer_pool_keep	Number of database blocks/latches in keep buffer pool
buffer_pool_recycle	Number of database blocks/latches in recycle buffer pool
circuits	max number of circuits
client_result_cache_lag	client result cache maximum lag in milliseconds
client_result_cache_size	client result cache max size in bytes
cluster_database	if TRUE startup in cluster database mode
cluster_database_instances	number of instances to use for sizing cluster db SGA structures
cluster_interconnects	interconnects for RAC use
commit_logging	transaction commit log write behaviour
commit_point_strength	Bias this node has toward not preparing in a two-phase commit
commit_wait	transaction commit log wait behaviour
commit_write	transaction commit log write behaviour
compatible	Database will be completely compatible with this software version
control_file_record_keep_time	control file record keep time in days
control_files	control file names list
control_management_pack_access	declares which manageability packs are enabled
core_dump_dest	Core dump directory
cpu_count	number of CPUs for this instance
create_bitmap_area_size	size of create bitmap buffer for bitmap index
create_stored_outlines	create stored outlines for DML statements
cursor_sharing	cursor sharing mode
cursor_space_for_time	use more memory in order to get faster execution
db_16k_cache_size	Size of cache for 16K buffers
db_2k_cache_size	Size of cache for 2K buffers
db_32k_cache_size	Size of cache for 32K buffers
db_4k_cache_size	Size of cache for 4K buffers
db_8k_cache_size	Size of cache for 8K buffers
db_block_buffers	Number of database blocks cached in memory
db_block_checking	header checking and data and index block checking
db_block_checksum	store checksum in db blocks and check during reads
db_block_size	Size of database block in bytes
db_cache_advice	Buffer cache sizing advisory
db_cache_size	Size of DEFAULT buffer pool for standard block size buffers
db_create_file_dest	default database location
db_create_online_log_dest_1	online log/controlfile destination #1
db_create_online_log_dest_2	online log/controlfile destination #2
db_create_online_log_dest_3	online log/controlfile destination #3
db_create_online_log_dest_4	online log/controlfile destination #4
db_create_online_log_dest_5	online log/controlfile destination #5
db_domain	directory part of global database name stored with CREATE DATABASE
db_file_multiblock_read_count	db block to be read each IO
db_file_name_convert	datafile name convert patterns and strings for standby/clone db
db_files	max allowable # db files
db_flashback_retention_target	Maximum Flashback Database log retention time in minutes.
db_keep_cache_size	Size of KEEP buffer pool for standard block size buffers
db_lost_write_protect	enable lost write detection
db_name	database name specified in CREATE DATABASE
db_recovery_file_dest	default database recovery file location
db_recovery_file_dest_size	database recovery files size limit
db_recycle_cache_size	Size of RECYCLE buffer pool for standard block size buffers
db_securefile	permit securefile storage during lob creation
db_ultra_safe	Sets defaults for other parameters that control protection levels
db_unique_name	Database Unique Name
db_writer_processes	number of background database writer processes to start
dbwr_io_slaves	DBWR I/O slaves
ddl_lock_timeout	timeout to restrict the time that ddls wait for dml lock
dg_broker_config_file1	data guard broker configuration file #1
dg_broker_config_file2	data guard broker configuration file #2
dg_broker_start	start Data Guard broker framework (DMON process)
diagnostic_dest	diagnostic base directory
disk_asynch_io	Use asynch I/O for random access devices
dispatchers	specifications of dispatchers
distributed_lock_timeout	number of seconds a distributed transaction waits for a lock
dml_locks	dml locks - one for each table modified in a transaction
drs_start	start DG Broker monitor (DMON process)
enable_ddl_logging	enable ddl logging
event	debug event control - default null string
fal_client	FAL client
fal_server	FAL server list
fast_start_io_target	Upper bound on recovery reads
fast_start_mttr_target	MTTR target in seconds
fast_start_parallel_rollback	max number of parallel recovery slaves that may be used
file_mapping	enable file mapping
fileio_network_adapters	Network Adapters for File I/O
filesystemio_options	IO operations on filesystem files
fixed_date	fixed SYSDATE value
gc_files_to_locks	mapping between file numbers and global cache locks
gcs_server_processes	number of background gcs server processes to start
global_context_pool_size	Global Application Context Pool Size in Bytes
global_names	enforce that database links have same name as remote database
global_txn_processes	number of background global transaction processes to start
hash_area_size	size of in-memory hash work area
hi_shared_memory_address	SGA starting address (high order 32-bits on 64-bit platforms)
hs_autoregister	enable automatic server DD updates in HS agent self-registration
ifile	include file in init.ora
instance_groups	list of instance group names
instance_name	instance name supported by the instance
instance_number	instance number
instance_type	type of instance to be executed
java_jit_enabled	Java VM JIT enabled
java_max_sessionspace_size	max allowed size in bytes of a Java sessionspace
java_pool_size	size in bytes of java pool
java_soft_sessionspace_limit	warning limit on size in bytes of a Java sessionspace
job_queue_processes	maximum number of job queue slave processes
large_pool_size	size in bytes of large pool
ldap_directory_access	RDBMS's LDAP access option
ldap_directory_sysauth	OID usage parameter
license_max_sessions	maximum number of non-system user sessions allowed
license_max_users	maximum number of named users that can be created in the database
license_sessions_warning	warning level for number of non-system user sessions
local_listener	local listener
lock_name_space	lock name space used for generating lock names for standby/clone database
lock_sga	Lock entire SGA in physical memory
log_archive_config	log archive config parameter
log_archive_dest	archival destination text string
log_archive_dest_1	archival destination #1 text string
log_archive_dest_10	archival destination #10 text string
log_archive_dest_2	archival destination #2 text string
log_archive_dest_3	archival destination #3 text string
log_archive_dest_4	archival destination #4 text string
log_archive_dest_5	archival destination #5 text string
log_archive_dest_6	archival destination #6 text string
log_archive_dest_7	archival destination #7 text string
log_archive_dest_8	archival destination #8 text string
log_archive_dest_9	archival destination #9 text string
log_archive_dest_state_1	archival destination #1 state text string
log_archive_dest_state_10	archival destination #10 state text string
log_archive_dest_state_2	archival destination #2 state text string
log_archive_dest_state_3	archival destination #3 state text string
log_archive_dest_state_4	archival destination #4 state text string
log_archive_dest_state_5	archival destination #5 state text string
log_archive_dest_state_6	archival destination #6 state text string
log_archive_dest_state_7	archival destination #7 state text string
log_archive_dest_state_8	archival destination #8 state text string
log_archive_dest_state_9	archival destination #9 state text string
log_archive_duplex_dest	duplex archival destination text string
log_archive_format	archival destination format
log_archive_local_first	Establish EXPEDITE attribute default value
log_archive_max_processes	maximum number of active ARCH processes
log_archive_min_succeed_dest	minimum number of archive destinations that must succeed
log_archive_start	start archival process on SGA initialization
log_archive_trace	Establish archivelog operation tracing level
log_buffer	redo circular buffer size
log_checkpoint_interval	# redo blocks checkpoint threshold
log_checkpoint_timeout	Maximum time interval between checkpoints in seconds
log_checkpoints_to_alert	log checkpoint begin/end to alert file
log_file_name_convert	logfile name convert patterns and strings for standby/clone db
max_commit_propagation_delay	Max age of new snapshot in .01 seconds
max_dispatchers	max number of dispatchers
max_dump_file_size	Maximum size (in bytes) of dump file
max_enabled_roles	max number of roles a user can have enabled
max_shared_servers	max number of shared servers
memory_max_target	Max size for Memory Target
memory_target	Target size of Oracle SGA and PGA memory
nls_calendar	NLS calendar system name
nls_comp	NLS comparison
nls_currency	NLS local currency symbol
nls_date_format	NLS Oracle date format
nls_date_language	NLS date language name
nls_dual_currency	Dual currency symbol
nls_iso_currency	NLS ISO currency territory name
nls_language	NLS language name
nls_length_semantics	create columns using byte or char semantics by default
nls_nchar_conv_excp	NLS raise an exception instead of allowing implicit conversion
nls_numeric_characters	NLS numeric characters
nls_sort	NLS linguistic definition name
nls_territory	NLS territory name
nls_time_format	time format
nls_time_tz_format	time with timezone format
nls_timestamp_format	time stamp format
nls_timestamp_tz_format	timestampe with timezone format
object_cache_max_size_percent	percentage of maximum size over optimal of the user session's object cache
object_cache_optimal_size	optimal size of the user session's object cache in bytes
olap_page_pool_size	size of the olap page pool in bytes
open_cursors	max # cursors per session
open_links	max # open links per session
open_links_per_instance	max # open links per instance
optimizer_capture_sql_plan_baselines	automatic capture of SQL plan baselines for repeatable statements
optimizer_dynamic_sampling	optimizer dynamic sampling
optimizer_features_enable	optimizer plan compatibility parameter
optimizer_index_caching	optimizer percent index caching
optimizer_index_cost_adj	optimizer index cost adjustment
optimizer_mode	optimizer mode
optimizer_secure_view_merging	optimizer secure view merging and predicate pushdown/movearound
optimizer_use_invisible_indexes	Usage of invisible indexes (TRUE/FALSE)
optimizer_use_pending_statistics	Control whether to use optimizer pending statistics
optimizer_use_sql_plan_baselines	use of SQL plan baselines for captured sql statements
os_authent_prefix	prefix for auto-logon accounts
os_roles	retrieve roles from the operating system
parallel_adaptive_multi_user	enable adaptive setting of degree for multiple user streams
parallel_automatic_tuning	enable intelligent defaults for parallel execution parameters
parallel_execution_message_size	message buffer size for parallel execution
parallel_instance_group	instance group to use for all parallel operations
parallel_io_cap_enabled	enable capping DOP by IO bandwidth
parallel_max_servers	maximum parallel query servers per instance
parallel_min_percent	minimum percent of threads required for parallel query
parallel_min_servers	minimum parallel query servers per instance
parallel_server	if TRUE startup in parallel server mode
parallel_server_instances	number of instances to use for sizing OPS SGA structures
parallel_threads_per_cpu	number of parallel execution threads per CPU
pga_aggregate_target	Target size for the aggregate PGA memory consumed by the instance
plscope_settings	plscope_settings controls the compile time collection, cross reference, and storage of PL/SQL source code identifier data
plsql_ccflags	PL/SQL ccflags
plsql_code_type	PL/SQL code-type
plsql_debug	PL/SQL debug
plsql_native_library_dir	plsql native library dir
plsql_native_library_subdir_count	plsql native library number of subdirectories
plsql_optimize_level	PL/SQL optimize level
plsql_v2_compatibility	PL/SQL version 2.x compatibility flag
plsql_warnings	PL/SQL compiler warnings settings
pre_page_sga	pre-page sga for process
processes	user processes
query_rewrite_enabled	allow rewrite of queries using materialized views if enabled
query_rewrite_integrity	perform rewrite using materialized views with desired integrity
rdbms_server_dn	RDBMS's Distinguished Name
read_only_open_delayed	if TRUE delay opening of read only files until first access
recovery_parallelism	number of server processes to use for parallel recovery
recyclebin	recyclebin processing
redo_transport_user	Data Guard transport user when using password file
remote_dependencies_mode	remote-procedure-call dependencies mode parameter
remote_listener	remote listener
remote_login_passwordfile	password file usage parameter
remote_os_authent	allow non-secure remote clients to use auto-logon accounts
remote_os_roles	allow non-secure remote clients to use os roles
replication_dependency_tracking	tracking dependency for Replication parallel propagation
resource_limit	master switch for resource limit
resource_manager_cpu_allocation	Resource Manager CPU allocation
resource_manager_plan	resource mgr top plan
result_cache_max_result	maximum result size as percent of cache size
result_cache_max_size	maximum amount of memory to be used by the cache
result_cache_mode	result cache operator usage mode
result_cache_remote_expiration	maximum life time (min) for any result using a remote object
resumable_timeout	set resumable_timeout
rollback_segments	undo segment list
sec_case_sensitive_logon	case sensitive password enabled for logon
sec_max_failed_login_attempts	maximum number of failed login attempts on a connection
sec_protocol_error_further_action	TTC protocol error continue action
sec_protocol_error_trace_action	TTC protocol error action
sec_return_server_release_banner	whether the server retruns the complete version information
serial_reuse	reuse the frame segments
service_names	service names supported by the instance
session_cached_cursors	Number of cursors to cache in a session.
session_max_open_files	maximum number of open files allowed per session
sessions	user and system sessions
sga_max_size	max total SGA size
sga_target	Target size of SGA
shadow_core_dump	Core Size for Shadow Processes
shared_memory_address	SGA starting address (low order 32-bits on 64-bit platforms)
shared_pool_reserved_size	size in bytes of reserved area of shared pool
shared_pool_size	size in bytes of shared pool
shared_server_sessions	max number of shared server sessions
shared_servers	number of shared servers to start up
skip_unusable_indexes	skip unusable indexes if set to TRUE
smtp_out_server	utl_smtp server and port configuration parameter
sort_area_retained_size	size of in-memory sort work area retained between fetch calls
sort_area_size	size of in-memory sort work area
spfile	server parameter file
sql92_security	require select privilege for searched update/delete
sql_trace	enable SQL trace
sql_version	sql language version parameter for compatibility issues
sqltune_category	Category qualifier for applying hintsets
standby_archive_dest	standby database archivelog destination text string
standby_file_management	if auto then files are created/dropped automatically on standby
star_transformation_enabled	enable the use of star transformation
statistics_level	statistics level STATISTICS_LEVEL and V$STATISTICS_LEVEL
streams_pool_size	size in bytes of the streams pool
tape_asynch_io	Use asynch I/O requests for tape devices
thread	Redo thread to mount
timed_os_statistics	internal os statistic gathering interval in seconds
timed_statistics	maintain internal timing statistics
trace_enabled	enable in memory tracing
tracefile_identifier	trace file custom identifier
transactions	max. number of concurrent active transactions
transactions_per_rollback_segment	number of active transactions per rollback segment
undo_management	instance runs in SMU mode if TRUE, else in RBU mode
undo_retention	undo retention in seconds
undo_tablespace	use/switch undo tablespace
use_indirect_data_buffers	Enable indirect data buffers (very large SGA on 32-bit platforms)
user_dump_dest	User process dump directory
utl_file_dir	utl_file accessible directories list
workarea_size_policy	policy used to size SQL working areas (MANUAL/AUTO)
xml_db_events	are XML DB events enabled