Hi Dears,
In this article, we will see Important Oracle v$parameters (SecureCRT)
Tab Name : v$parameter
Button Name : SGA
|
1 2 3 4 5 |
col name for a50 set linesize 300 col value for a40 select name,value from v$parameter where name like '%sga%'; select &sga/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 6 7 8 |
NAME VALUE -------------------------------------------------- ---------------------------------------- sga_max_size 4294967296 pre_page_sga TRUE lock_sga FALSE allow_group_access_to_sga FALSE sga_target 4294967296 unified_audit_sga_queue_size 1048576 |
|
1 2 3 4 5 6 |
select &sga/1024/1024/1024 GB from dual; Enter value for sga: 4294967296 GB ---------- 4 |
Button Name : SGA_TARGET
|
1 2 3 4 5 |
col name for a50 set linesize 300 col value for a40 select name,value from v$parameter where name like '%sga_target%'; select &sga_target/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 6 7 8 9 10 |
NAME VALUE -------------------------------------------------- ---------------------------------------- sga_target 4294967296 select &sga_target/1024/1024/1024 GB from dual; Enter value for sga_target: 4294967296 GB ---------- 4 |
Button Name : PGA_Aggregate_target
|
1 2 3 4 5 |
SQL> col name for a50 SQL> set linesize 300 SQL> col value for a40 select name,value from v$parameter where name like '%pga_aggregate_target%'; select &pga_aggregate_target/1024/1024/1024 GB from dual; |
Output :
|
1 2 3 4 5 |
SQL> select &pga_aggregate_target/1024/1024/1024 GB from dual; NAME VALUE -------------------------------------------------- ---------------------------------------- pga_aggregate_target 2147483648 |
|
1 2 3 4 5 |
SQL> Enter value for pga_aggregate_target: 2147483648 GB ---------- 2 |
Button Name : processes
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
set linesize 300 col resource_name for a50 SELECT Instance_number,RESOURCE_NAME, MAX(MAX_UTILIZATION) MAX_PROCESSES FROM DBA_HIST_RESOURCE_LIMIT WHERE RESOURCE_NAME = 'processes' GROUP BY instance_number,RESOURCE_NAME; INSTANCE_NUMBER RESOURCE_NAME MAX_PROCESSES --------------- -------------------------------------------------- ------------- 1 processes 113 2 processes 91 select * from gv$resource_limit where RESOURCE_NAME = 'processes' order by 1; INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION LIMIT_VALUE CON_ID ---------- -------------- ------------------- --------------- ------------------------------------------ 1 processes 98 113 200 200 0 2 processes 68 91 200 200 0 |
Button Name : GV_SGA_Memory
|
1 2 3 4 |
set linesize 300 col name for a30 col value for a30 select inst_id,NAME,VALUE/1024/1024/1024 Value_in_GB from gv$parameter where name in ('sga_max_size','sga_target','memory_target') order by 1; |
Output :
|
1 2 3 4 5 6 7 8 |
INST_ID NAME VALUE_IN_GB ---------- ------------------------------ ----------- 1 sga_target 4 1 sga_max_size 4 1 memory_target 0 2 sga_target 4 2 sga_max_size 4 2 memory_target 0 |
Button Name : Check DB Character Set
|
1 2 3 4 |
set linesize 300 col parameter for a40 col value for a40 select * from nls_database_parameters where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET'); |
Output :
|
1 2 3 4 |
PARAMETER VALUE ---------------------------------------- ---------------------------------------- NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_CHARACTERSET AL32UTF8 |
Button Name : Hidden Parameters
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(instance_name, 17) current_instance FROM v$instance; SET TERMOUT ON; SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 256 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN ksppinm FORMAT a55 HEAD 'Parameter Name' COLUMN ksppstvl FORMAT a40 HEAD 'Value' COLUMN ksppdesc FORMAT a60 HEAD 'Description' TRUNC SELECT ksppinm , ksppstvl , ksppdesc FROM x$ksppi x , x$ksppcv y WHERE x.indx = y.indx AND TRANSLATE(ksppinm,'_','#') like '#%' ORDER BY ksppinm; |
Button Name : Hidden PARAMETER for Specific Value
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 256 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN ksppinm FORMAT a55 HEAD 'Parameter Name' COLUMN ksppstvl FORMAT a40 HEAD 'Value' COLUMN ksppdesc FORMAT a60 HEAD 'Description' TRUNC set linesize 300 SELECT ksppinm , ksppstvl , ksppdesc FROM x$ksppi x , x$ksppcv y WHERE x.indx = y.indx and x.ksppinm = '&value' AND TRANSLATE(ksppinm,'_','#') like '#%' ; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Note: Please test scripts in Non Prod before trying in Production.
Loading...
