DATABASE_PROPERTIES
DATABASE_PROPERTIES is a data dictionary view which is able to describe the nature of a database by collecting its permanent properties
Let's see standard properties of a database.
SQL> set pagesize 100;
SQL> column property_name format a30;
SQL> column property_value format a40;
SQL> select property_name, property_value from database_properties order by 1;
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DBTIMEZONE +00:00
DEFAULT_EDITION ORA$BASE
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TBS_TYPE SMALLFILE
DEFAULT_TEMP_TABLESPACE TEMP
DICT.BASE 2
DICTIONARY_ENDIAN_TYPE LITTLE
DST_PRIMARY_TT_VERSION 32
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
EXPORT_VIEWS_VERSION 8
Flashback Timestamp TimeZone GMT
GLOBAL_DB_NAME ORCLPDB
LOCAL_UNDO_ENABLED TRUE
MAX_PDB_SNAPSHOTS 8
MAX_PDB_STORAGE UNLIMITED
MAX_SHARED_TEMP_SIZE UNLIMITED
MAX_STRING_SIZE STANDARD
NLS_CALENDAR GREGORIAN
NLS_CHARACTERSET AL32UTF8
NLS_COMP BINARY
NLS_CURRENCY $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_DUAL_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_LANGUAGE AMERICAN
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS .,
NLS_RDBMS_VERSION 21.0.0.0.0
NLS_SORT BINARY
NLS_TERRITORY AMERICA
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NO_USERID_VERIFIER_SALT B1C8F128459F84EA6068906C6368712F
NO_USERID_VERIFIER_SALT_COPY B1C8F128459F84EA6068906C6368712F
OLS_OID_STATUS 0
TDE_MASTER_KEY_ID
WORKLOAD_CAPTURE_MODE
WORKLOAD_REPLAY_MODE
44 rows selected.
Let's explain some properties we usually use.
- DBTIMEZONE
- DEFAULT_PERMANENT_TABLESPACE
- DEFAULT_TEMP_TABLESPACE
- GLOBAL_DB_NAME
- NLS Parameters
There's a same name function called DBTIMEZONE can be use to display the database timezone.
SQL> select dbtimezone from dual;
DBTIME
------
+00:00
This makes us to use the information flexibly, no need to query DATABASE_PROPERTIES.
This is the default tablespace of the database for new user who was created without specifying any permanent tablespace.
The most commonly used name for the default permanent tablespace is USERS. To change it, you can use ALTER DATABASE DEFAULT TABLESPACE statement.
This is the default temporary tablespace of the database for new user who was created without specifying any temporary tablespace.
The most commonly used name for the default temporary tablespace is TEMP. To change it, you can use ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement.
Although GLOBAL_DB_NAME looks like DB_NAME or DB_UNIQUE_NAME, they have different definitions.
That is to say, GLOBAL_DB_NAME and DB_NAME have the same value if the database didn't define DB_DOMAIN.
The easiest way to check global database name is to query GLOBAL_NAME, a convenient data dictionary view.
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
ORCLPDB
Most of NLS parameters here come from NLS_DATABASE_PARAMETERS, which are unchangeable after database creation.