Oracle variable settings

I. View and set Oracle character sets
1: view the current character set parameter settings of the Database Server
Select * from V $ nls_parameters;
Or select * From nls_database_parameters
Or select userenv ('language') from dual;
Query Result
Nls_characterset indicates the character set,
Nls_nchar_characterset indicates the National Character Set
Nls_language
Nls_territory Region
Nls_currency local currency character
Nls_iso_currency ISO currency character
The nls_numeric_characters decimal characters are separated by groups.
Nls_characterset Character Set
Nls_calendar calendar system
The default date format of nls_date_format
Default date language of nls_date_language
Nls_sort character sorting Sequence
Nls_time_format
Nls_timestamp_format
The preceding fields can be modified using alter system set.
Note: nls_database_parameters indicates the character set of the database. It is set during database creation and will not be changed.
V $ nls_parameters displays the current session value, which is controlled by the NLS client.
Therefore, the query results of the two views may be different.

2. Modification Method
You can use the alter database character set statement to modify the character set. However, there are limits on modifying the character set after the database is created. Only when the new character set is the current character set, the character set of the database can be modified, for example, utf8 is a superset of us7ascii. You can use alter database character set utf8 to modify the character set of a database.
Al32utf8, utf8, and utfe are UTF-8 encoding character sets

3. view available Character Set parameter settings in the database
Select * from V $ nls_valid_values;

4: client Character Set Environment
Select * From nls_instance_parameters:
It is derived from V $ parameter, which indicates the character set setting of the client, which may be a parameter file, environment variable, or registry.

5: Session Character Set Environment
Select * From nls_session_parameters
The source is V $ nls_parameters, which indicates the session's own settings. It may be the session's environment variable or the alter session is completed. If the session has no special settings, it will be consistent with nls_instance_parameters.

Note: The Character Set of the client end is set through the Environment Variable nls_lang. If the check result shows that the character set of the server end is inconsistent with that of the client end, garbled characters may occur.

Ii. Date Format settings
Select to_date ('12-Jan-1985 11:12:11 am ', 'dd-mon-yyyy hh: MI: SS am') from dual
Show invalid month
Solution: Alter session set nls_date_language = American

Iii. initialization parameter settings
Select * from V $ parameter; more than two hundred Parameters
Alter session modify the parameter settings of the current session
Alter system to modify the parameter settings of all sessions can be executed only by privileged users or DBA users.
Select name, type, isses_modifiable, issys_modifiable from V $ parameter;
Where isses_modifiable identifies whether the initialization parameter can be repaired using alter_session
Where issys_modifiable identifies whether the initialization parameter can be repaired using alter_system

For example, modify nls_date_language.
Alter system set nls_date_language = American

4. view the system parameters of the Oracle database
V $ controlfile: control file information;
V $ datafile: data file information;
V $ log: Log File Information;
V $ process: processor information;
V $ session: session information;
V $ transaction: transaction information;
V $ Resource: resource information;
V $ SGA: information about the global zone of the system.

5. modify a user
1: change the password
Alter User Username identified by "123456"

2: Modify the user's default tablespace
Alter User Username default tablespace users

3: Lock the user
Alter User Username account lock;
Alter User Username account unlock;

4. delete a user
Drop User Username;

Drop User Username cascade (delete all users and their created entities );

Vi. sqlplus settings (set, show command)

SQL> show all -- View All 68 system variable values

SQL> show user -- display the current connected user

SQL> show error -- Display Error

SQL> set heading off -- disable the output column title. The default value is on.

SQL> set feedback off -- disable counting feedback of the last row. The default value is "send back on" for 6 or more records"

SQL> set timing on -- the default value is off. It sets the query time, which can be used to estimate the SQL statement execution time and test the performance.

SQL> set sqlprompt "SQL>" -- sets the default prompt. The default value is "SQL>"

SQL> set linesize 1000 -- set the row width on the screen. The default value is 100.

SQL> set autocommit on -- sets whether to submit automatically. The default value is off.

SQL> set pause on -- the default value is off. When this parameter is set to suspend, the screen will be stopped. Wait for the Enter key to display the next page.

SQL> set arraysize 1 -- default value: 15

SQL> set long 1000 -- the default value is 80.