SQL Script to list all parameters oracle

Below is the sql script to get oracle database parameters for database performance tuning and information collection. For example V$PARAMETER displays information about the initialization parameters that are currently in effect for the session.

set pages 100
spool getoraparams.lst

prompt User running the script?

show user;
PROMPT *** Oracle Version ***
SELECT BANNER FROM v$version;

PROMPT *** Installed Components ***
col comp_name for a40
col version for a15
col status for a10
SELECT comp_name, version, status FROM dba_registry;



PROMPT *** V$ parameters ***
col name for a40
col value for a30
SELECT name, value FROM v$parameter;

PROMPT *** NLS Parameters ***
SELECT * FROM nls_database_parameters;

PROMPT *** Tablespace List ***
set line 2000
SELECT * FROM DBA_TABLESPACES

PROMPT *** Tablespace Details ***
select a.tablespace_name, a.block_size, a.initial_extent, a.next_extent, a.extent_management, a.segment_space_management
, b.file_name, b.bytes/1024/1024 "Bytes in MB", b.autoextensible
from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name;

PROMPT *** DBA Scheduler Jobs Details ***
select * from dba_scheduler_jobs;

PROMPT *** DBA Scheduler Jobs Details ***
select * from dba_jobs;


spool off
quit;

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