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 ***

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

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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.