Useful Oracle Data Dictionary Views

select * from v$pwfile_users

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
WI                             TRUE  FALSE FALSE

Advertisements

How To See Oracle Database Parameter

You can run this sql to see Oracle Database Parameter :

SELECT name,
value,
isdefault default_value,
description,
isses_modifiable session_modifiable,
issys_modifiable system_modifiable,
isdeprecated deprecated,
update_comment
FROM v$parameter
ORDER BY name

ISDEFAULT Indicates whether the parameter is set to the default value (TRUE) or the parameter value was specified in the parameter file (FALSE)
ISSES_MODIFIABLE Indicates whether the parameter can be changed with ALTER SESSION (TRUE) or not (FALSE)
ISSYS_MODIFIABLE Indicates whether the parameter can be changed with ALTER SYSTEM and when the change takes effect:
IMMEDIATE – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect immediately.
DEFERRED – Parameter can be changed with ALTER SYSTEM regardless of the type of parameter file used to start the instance. The change takes effect in subsequent sessions.
FALSE – Parameter cannot be changed with ALTER SYSTEM unless a server parameter file was used to start the instance. The change takes effect in subsequent instances.
ISINSTANCE_MODIFIABLE For parameters that can be changed with ALTER SYSTEM, indicates whether the value of the parameter can be different for every instance (TRUE) or whether the parameter must have the same value for all Real Application Clusters instances (FALSE). If the ISSYS_MODIFIABLE column is FALSE, then this column is always FALSE.
ISMODIFIED Indicates whether the parameter has been modified after instance startup:
MODIFIED – Parameter has been modified with ALTER SESSION
SYSTEM_MOD – Parameter has been modified with ALTER SYSTEM (which causes all the currently logged in sessions’ values to be modified)
FALSE – Parameter has not been modified after instance startup
ISADJUSTED Indicates whether Oracle adjusted the input value to a more suitable value (for example, the parameter value should be prime, but the user input a non-prime number, so Oracle adjusted the value to the next prime number)
ISDEPRECATED Indicates whether the parameter has been deprecated (TRUE) or not (FALSE)

And this sql to compare Oracle Database Parameter between 2 database :

SELECT a.name mdn_name, b.name jkt_name, a.VALUE mdn_value, b.VALUE jkt_value
FROM v$parameter a, v$parameter@db2 b
WHERE a.name(+) = b.name
ORDER BY a.name