[Oracle11g, 8] data dictionary and Character Set, oracle11g Character Set

[Oracle11g, 8] data dictionary and Character Set, oracle11g Character Set
I. Data Dictionary: oracle's data dictionary records the metadata of database management. It is a core file of life and death for databases. 1. Place the data dictionary in the system tablespace. the user is sys. 2. Describe the definition of databases and objects. 3. The data dictionary is read-only. 4. It is maintained by the server and can only be queried. 5. Most of them are created by catalog. SQL.
Generally, a data dictionary consists of four parts: an internal RDBMS (X $) table, a data dictionary table, a dynamic performance (V $) view, and a data dictionary view.

Ii. Data Dictionary composition:
1. Internal RDBMS (X $) Table
X $ tables are the core part of Oracle databases. These tables are used to track internal database information and maintain normal database operation. X $ tables are encrypted and named, and Oracle does not provide instructions. This knowledge is a technical secret of Oracle, oracle uses these X $ to create a large number of other views for users to query and manage databases. However, because the X $ table records a large amount of useful information, it is constantly explored by global DBAs. The most well-known ones are X $ BH and X $ KSMSP.
2. Data dictionary table is used to store information about tables, indexes, constraints, and other database structures. These objects usually end with "$" (for example, TAB $, OBJ $, and TS $) and are created by running the SQL. bsq script when creating a database.

3. static Data Dictionary view because X $ tables and data dictionary tables cannot be directly accessed, Oracle creates a static data dictionary view for users to access data dictionary information, because the information is relatively stable and cannot be directly modified, it is also called a static data dictionary view. The data dictionary view is created by the catalog. SQL script (in the $ ORACLE_HOME/rdbms/admin/directory) When you create a database.
①. The static data dictionary view is generally divided into three categories based on different prefixes:. the USER _ Class View contains information about objects owned by users. You can use this view to query information about objects owned by users. B. The ALL _ Class View contains information about ALL objects that the user has permission to access. C. DBA _ Class View contains information about all related objects in the database. You need the select any table permission to access the database.
②. Examples of common data dictionary views. DICT/DICTIONARY for easy retrieval, Oracle provides a view named DICTIONARY. Based on this view, Oracle creates two synonyms named DICT and DICTIONARY:
B. DICT_COLUMNS is similar to DICT. The DICT_COLUMNS view records the dictionary View COLUMN and its description:

C. OBJ $/DBA_OBJECTS/OBJ $ is an underlying dictionary table that records the information of all objects in the database. The DBA_OBJECTS view is based on OBJ $ and is in the same line, the ALL_OBJECTS and USER_OBJECTS views are also created.
4. Dynamic Performance View:
The Dynamic Performance View (V $) View records the database runtime information and statistical data. Most Dynamic Performance views are updated in real time to reflect the current status of the database. Oracle displays the status of the Oracle database through the dynamic performance view and provides it to users and database administrators. Oracle provides detailed documentation on the V $ view for developers to refer, it is the main basis for us to study and manage databases.
①. When the GV $ and V $ views are started, Oracle dynamically creates the X $ table. Based on this, Oracle creates the GV $ and V $ views. Starting from Oracle8, the GV $ view is introduced and its meaning is Global V $. Except for some special cases, each V $ view has a corresponding GV $ view. The GV $ view is generated to meet the needs of the OPS/RAC environment. In the OPS/RAC environment, query the GV $ view to return information of all instances, each V $ view is based on the GV $ view. The WHERE condition of the INST_ID column is added to create a view that only contains information about the currently connected instance. To sum up, the GV $ view and V $ view of Oracle are created during the database creation process and are built into the database. Oracle presents these definitions through the v $ fixed_view_definition view.
②. GV _ $, V _ $ view and V $, GV $ Synonym
After GV $ and V $, Oracle creates GV _ $ and V _ $ views, and then creates public synonyms for these views. These operations are implemented through the catalog. SQL script (which is located in the $ ORACLE_HOME/rdbms/admin/directory.
From catalog. extract an excerpt from the SQL Script: create or replace view v _ $ fixed_table as select * from v $ fixed_table; create or replace public synonym v $ fixed_table for v _ $ fixed_table; create or replace view gv _ $ fixed_table as select * from gv $ fixed_table; create or replace public synonym gv $ fixed_table for gv _ $ fixed_table;
In fact, most users access the V $ object, which is not a view, but a synonym for the V _ $ view; the V _ $ view is created based on the real V $ view (which is created based on the X $ table.
5. The dynamic performance view and database startup because the dynamic performance view are automatically created during database startup, the views we can access vary in different stages of database startup. ①. In the Nomount stage, when the database is started to the nomount state, only the database instance is started. The instance information mainly comes from the parameter file. Therefore, you can query the information recorded in the parameter file, the following is the main view of information available at this stage: V $ PARAMETER, V $ spparameter v $ SGA, V $ SGASTAT, V $ BH, V $ instance v $ OPTION, V $ version v $ PROCESS, V $ SESSION ②. in the Mount phase, when the database is started to the Mount state, the control file is read, and views related to the control file can be queried at this time. The following is the main view of information available in this phase: V $ THREAD, V $ CONTROLFILE, V $ DATABASE, V $ DATAFILE, V $ LOGFILE, V $ DATAFILE_HEADER
③ In the Open stage, when the database is Open, all the dynamic performance views and data dictionaries can be queried.



2. synonyms in the data dictionary:
SQL> select file #, name from v $ datafile; (Synonym) FILE # NAME ---------- upload 1/u01/app/oracle/oradata/prod/disk3/system01.dbf 2/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf 3/u01/app /oracle/oradata/prod/disk3/undotbs01.dbf 4/u01/app/oracle/oradata/prod/disk3/users01.dbf
SQL> select file #, name from v _ $ datafile;
FILE # NAME ---------- upload 1/u01/app/oracle/oradata/prod/disk3/system01.dbf 2/u01/app/oracle/oradata/prod/disk3/sysaux01.dbf 3/u01/app /oracle/oradata/prod/disk3/undotbs01.dbf 4/u01/app/oracle/oradata/prod/disk3/users01.dbf

3. fuzzy query view name select * from dictionary where table_name like 'dba ';

Iv. Data Dictionary view: (important)
Data Dictionary view classification, static and dynamic)
1. Most of the dynamic views can be accessed under the mount, reflecting the real-time status of the database. Most of them start with v $ and are read from the control file and memory. Find the names of all dynamic views from the view v $ fixed_table. For optimization and database monitoring. V $ _ dynamic view of a single singular instance. Gv $ _ dynamic view of the singular rac Environment

SQL> select count (*) from v $ fixed_table;
COUNT (*) ---------- 1396
Common static views: select name from v $ tablespace; select file #, name from v $ datafiles; select * from v $ log; select * from v $ logfile; select name from v $ archived_log;


2. Access the static view in the open state of the database to learn about the physical structure of the database. Most of them start with dba, all, and user and use the complex form user _: to store information about the objects owned by the current user all _: to store the objects accessible to the current user dba _: store information of all user objects (only sys/system users can access the object by default)
Example: view the partition Information select segment_name, segment_type, tablespace_name, bytes/1024 k, extents, blocks from dba_segments; # view the data file SQL> select file_id, file_name, tablespace_name, bytes/1024/1024 m from dba_data_files;



V. Character Set:














What character sets are in the oracle database? What is the relationship between Subsets and supersets?

Understanding ORACLE Database Character Set

I. Introduction

ORACLE Database character set (Globalization Support), or national language Support (NLS), is used to store, process, and retrieve data in their own languages and formats. With global support, ORACLE provides users with familiar database mother-tongue environments, such as date formats, digital formats, and storage sequences. Oracle supports multiple languages and character sets. oracle8i supports 48 languages, 76 countries, and 229 character sets, oracle9i supports 57 languages, 88 countries and regions, and 235 character sets. Due to the wide variety of oracle character sets, and the multiple links in the storage, retrieval, and migration of oracle Data are closely related to Character Set settings, in practical applications, database developers and administrators often encounter oracle Character Set problems. This article briefly analyzes the oracle character set through the following aspects:

Ii. Basic Character Set knowledge

2.1 Character Set
In essence, according to a certain character encoding scheme, assign a specific set of symbols to different numerical encoding sets. The earliest supported encoding scheme of Oracle Database is US7ASCII.
The character set naming rules of Oracle follow the following naming rules:

Namely: <language> <bit number> <encoding>
For example, ZHS16GBK indicates that the GBK encoding format and 16-bit (two-byte) simplified Chinese character set are used.

2.2 character encoding scheme
2.2.1 single-byte encoding
(1) single-byte 7-bit character set, which can be 128 characters. The most common character set is US7ASCII.
(2) single-byte 8-bit character set, which can be defined as 256 characters, suitable for most European countries
Example: WE8ISO8859P1 (Western Europe, 8-bit, ISO standard 8859P1 encoding)
2.2.2 multi-byte encoding
(1) variable-length multi-byte encoding
Some characters are represented by one byte. Other characters are represented by two or more characters. Long-length multi-byte encoding is commonly used for Asian languages, such as Japanese, Chinese, and Hindi.
For example, AL32UTF8 (where AL stands for ALL, which applies to ALL languages), zhs16cgb231280
(2) fixed length multi-byte encoding
Each character uses a fixed-length multi-byte encoding scheme. Currently, the only fixed-length multi-byte encoding supported by oracle is AF16UTF16, which is only used for national character sets.
2.2.3 unicode encoding
Unicode is a single encoding scheme that covers all the known characters currently used around the world, that is, Unicode provides a unique encoding for each character. UTF-16 is a unicode 16-bit encoding method, a fixed length multi-byte encoding, with 2 bytes representing a unicode character, AF16UTF16 is the UTF-16 encoding character set.
UTF-8 is unicode 8-bit encoding, is a variable-length multi-byte encoding, this encoding can use 1, 2, 3 bytes to represent a unicode character, AL32UTF8, UTF8 and UTFE are UTF-8 encoded character sets

2.3 character set super
When the encoding value of A character set (character set A) contains the encoding value of all other character sets (Character Set B), and the same encoding value of the two character sets represents the same character, character Set A is the Super character of Character Set B, or Character Set B is the subset of Character Set.
In the official documents of Oracle8i and oracle9i, the subset-superset pairs table is provided. For example, WE8ISO8859P1 is a subset of WE8MSWIN1252. Because US7ASCII is the earliest Oracle Database encoding format, many character sets are supersets of US7ASCII. For example, WE8ISO8859P1, ZHS16CGB231280, and ZHS16GBK are all U ...... the remaining full text>

How to change the Oracle Character Set AL32UTF8 to ZHS16GBK

1. Modify the server character set (not recommended)

Before oracle 8, you can directly modify the data dictionary table props $ to change the character set of the database. However, after oracle8, at least three system tables record the information of the database character set. modifying only the props $ table is incomplete and may cause serious consequences. The correct modification method is as follows:

$ Sqlplus/nolog

SQL> conn/as sysdba;

The above method cannot be tested. Use scott/tiger to log on to sqlplus, connect sys/sys as sysdba, and enter the command.

If the database server has been started, run the shutdown immediate command to shut down the database server, and then run the following command:

SQL> STARTUP MOUNT;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK; // skip super subset Detection

SQL> ALTER DATABASE national CHARACTER SET INTERNAL ZHS16GBK;

This line does not work, an error occurred after execution ORA-00933: the SQL command ended incorrectly, but the execution of the previous line has taken effect, other articles do not mention this line.

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP