Query for Oracle tablespace and parameter details

Oracle Database
-- Script to Capture DB Parameters
-- 
set heading off
set line 300

select *
from
(
select 'NLS_LANGUAGE              => '||value curvalue from nls_database_parameters where parameter = upper('nls_language')
union
select 'NLS_CHARACTERSET          => '||value curvalue from nls_database_parameters where parameter = upper('nls_characterset')
union
select 'NLS_NCHAR_CHARACTERSET    => '||value curvalue from nls_database_parameters where parameter = upper('nls_nchar_characterset')
union
select 'NLS_DATE_FORMAT           => '||value curvalue from nls_database_parameters where parameter = upper('nls_date_format')
union
select 'NLS_TERRITORY             => '||value curvalue from nls_database_parameters where parameter = upper('nls_territory')
union
select 'DB LENGTH SEMANTICS       => '||value curvalue from nls_database_parameters where parameter='NLS_LENGTH_SEMANTICS'
union
select 'INSTANCE LENGTH SEMANTICS => '||value curvalue  from nls_instance_parameters where parameter='NLS_LENGTH_SEMANTICS'
union
select 'SESSION LENGTH SEMANTICS  => '||value curvalue  from nls_session_parameters where parameter='NLS_LENGTH_SEMANTICS'
union
select 'DB_BLOCK_SIZE             => '||value curvalue from v$parameter where name = 'db_block_size'
union
select 'LARGE_POOL_SIZE           => '||value curvalue from v$parameter where name = 'large_pool_size'
union
select 'OPEN_CURSORS              => '||value curvalue from v$parameter where name = 'open_cursors'
union
select 'PROCESSES                 => '||value curvalue from v$parameter where name = 'processes'
union
select 'CURSOR_SHARING            => '||value curvalue from v$parameter where name = 'cursor_sharing'
union
select 'DB_CACHE_SIZE             => '||value curvalue from v$parameter where name = 'db_cache_size'
union
select 'OPTIMIZER_MODE            => '||value curvalue from v$parameter where name = 'optimizer_mode'
union
select 'SHARED_POOL_SIZE          => '||value curvalue from v$parameter where name = 'shared_pool_size'
union
select 'TIMED_STATISTICS          => '||value curvalue from v$parameter where name = 'timed_statistics'
union
select 'OPTIMIZER_INDEX_COST_ADJ  => '||value curvalue from v$parameter where name = 'optimizer_index_cost_adj'
) order by curvalue;

set heading on



-- Get Tablespace information
 select a.TABLESPACE_NAME "TableSpace Name",
       round(a.BYTES / 1024 / 1024) "MB Allocated",
       round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used",
       nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free",
       round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) "Pct Used",
       round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) "Pct Free"
from   (select   TABLESPACE_NAME,
                 sum(BYTES) BYTES
        from     sys.dba_data_files
        group by TABLESPACE_NAME) a,
       (select   TABLESPACE_NAME,
                 sum(BYTES) BYTES
        from     sys.dba_free_space
        group by TABLESPACE_NAME) b
where  a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
order  by ((a.BYTES-b.BYTES)/a.BYTES) desc;

In case of any ┬ęCopyright or missing credits issue please check CopyRights page for faster resolutions.

Leave a Reply