A database must preserve the characters an application intends to save. The word café includes an accented é. Keeping that accent is part of keeping the original value.
Oracle DBA Lesson 21 Part 1 — How Oracle Stores Text
This lesson concerns ordinary application text in a VARCHAR2 column using the database character set.
Characters, bytes and encoding
Characters express written text: letters, punctuation, digits, and symbols. Computers represent data using bytes. A byte contains eight binary digits. Encoding supplies the rules that connect characters with a representation made from bytes.
The database character set determines the supported characters and the encoding used for database-character-set text. Its collection of supported characters is its repertoire. Oracle interprets valid encoded text according to those rules when the value is read.
The video's route models representation:
- The application supplies café.
- The database-character-set rules supply its encoded representation.
- Physical database data files on disk retain persistent data.
- The application later retrieves text corresponding to that representation.
The original text and the stored value remain available. Encoding changes representation. Reading produces returned text while retaining the stored value. The drawn data symbols have no literal byte values or byte counts.
This diagram does not describe physical write timing. A SQL statement, a commit, and the writing of a changed database block to a data file have their own mechanisms. This part focuses on text representation. The SGA, redo, and database-writer sequence were covered earlier.
Character coverage
Basic English text such as menu fits a limited repertoire like US7ASCII. The accented é in café falls outside that repertoire.
When conversion targets a character set that lacks a required character, replacement can lose part of the original spelling. The exact replacement or symptom depends on the conversion and the client conditions.
Gather representative application text: descriptions, words, symbols, and languages. Consider present requirements and expected growth. Check them against the proposed character-set coverage.
Unicode and the new-database choice
Unicode provides shared character codes for many writing systems. AL32UTF8 implements Unicode UTF-8 and is Oracle's recommended database character set for new deployments. It supports café and broad multilingual text.
Keep the exact identifier AL32UTF8 in a creation specification. Oracle also has a legacy character set named UTF8, with a different encoding implementation. Treat them as distinct Oracle identifiers. Unicode support is release-dependent. Oracle 19c documentation lists Unicode 9.0 for AL32UTF8. Broad coverage is not a guarantee of every character added to later Unicode releases.
Inspect an existing database's actual configuration. A new-database recommendation does not establish its current setting. Character-set migration requires a separate assessment and procedure.
See Supporting Multilingual Databases with Unicode and Creating and Configuring an Oracle Database.
Setting check
Confirm the intended session and container:
SHOW USER
SHOW CON_NAME
SELECT value
FROM nls_database_parameters
WHERE parameter = 'NLS_CHARACTERSET';SHOW USER and SHOW CON_NAME are SQL*Plus commands. They identify the connected account and container. The query selects the database-character-set parameter value from NLS_DATABASE_PARAMETERS.
Record the actual returned value. If it is AL32UTF8, that is the database-character-set setting in the inspected context. Other values need their own coverage review. The query does not demonstrate that a client correctly sends or displays text.
Use the intended pluggable database's context in a multitenant system. Do not assume that every pluggable database uses the root's character set. Database-versus-national datatype selection belongs to Part 2. Character and byte limits, SQL*Plus client encoding, and session formatting are separate later outcomes.
Recap
The database character set supplies the repertoire and the encoding rules for ordinary VARCHAR2 text. A byte contains eight binary digits, and encoding connects characters such as café to a representation made from bytes. The stored value remains available, and reading returns text from that representation. The diagram assigns no literal byte values or byte counts, and it does not describe when a changed block is written. A limited repertoire such as US7ASCII can store menu and still omit the accented é in café. Conversion into a character set that lacks a required character can replace part of the original spelling. AL32UTF8 implements Unicode UTF-8 and is Oracle's recommended database character set for a new deployment. Keep it distinct from the legacy character set UTF8. Oracle 19c documentation lists Unicode 9.0 for AL32UTF8, and that coverage does not include every character added in later Unicode releases. A recommendation for a new database does not establish an existing database's setting. In the intended container, the read-only query of NLS_CHARACTERSET reports the actual parameter value. It does not show that a client sends or displays text correctly, and pluggable databases need not share the root's character set.
No comments:
Post a Comment