Migrer des utilisateurs Oracle® vers Cloud SQL pour PostgreSQL : types de données, utilisateurs et tables

Ce document fait partie d'une série qui fournit des informations et des conseils clés sur la planification et l'exécution des migrations de base de données Oracle® 11g/12c vers Cloud SQL pour PostgreSQL version 12. Outre la section d'introduction à la configuration, la série inclut les éléments suivants :

Types de données

Cloud SQL pour PostgreSQL fournit plusieurs types de données qui sont totalement équivalents ou semblables aux types de données fournis par Oracle. Le tableau suivant répertorie les types de données PostgreSQL les plus courants, suivis d'une comparaison entre les types de données primitifs Oracle et les types de données Cloud SQL pour PostgreSQL correspondants. Lorsqu'un type de données n'est pas compatible, un autre type de données possible est répertorié.

Les 12 types de données primitifs Cloud SQL pour PostgreSQL

Famille de type de données Cloud SQL pour PostgreSQL Nom du type de données Cloud SQL pour PostgreSQL Spécification du type de données
Chaîne/caractère CHAR(n) Stocke exactement n caractères. Alias de CHARACTER(n).
CHARACTER(n) Stocke exactement n caractères.
VARCHAR(n) Stocke un nombre variable de caractères, jusqu'à un maximum de n caractères. Alias de CHARACTER VARYING(n).
CHARACTER VARYING(n) Stocke un nombre variable de caractères, jusqu'à un maximum de n caractères.
TEXT Variante spécifique de VARCHAR qui ne nécessite pas de limite maximale du nombre de caractères. La chaîne de caractères la plus longue possible pouvant être stockée est de 1 Go (identique pour tous les types de données de chaîne de caractères).
Numérique SMALLINT La valeur minimale est -32768 | La valeur maximale est 32767.
INTEGER La valeur minimale est -217483648 | La valeur maximale est 2147483647.
BIGINT La valeur minimale est 2^63 | La valeur maximale est 2^63-1.
REAL Nombre à virgule flottante à simple précision (4 octets).
DOUBLE PRECISION Nombre à virgule flottante à double précision (8 octets).
DECIMAL (p,s) Possibilité de stocker n'importe quelle valeur avec p chiffres et s décimales.
NUMERIC(p,s) Possibilité de stocker n'importe quelle valeur avec p chiffres et s décimales.
SMALLSERIAL Entier à incrémentation automatique. La valeur minimale est 1 | La valeur maximale est 32 767.
SERIAL Entier à incrémentation automatique. La valeur minimale est 1 | La valeur maximale est 2147483647.
BIGSERIAL Entier à incrémentation automatique. La valeur minimale est 1 | La valeur maximale est 2^63-1.
Monétaire MONEY Montant en devise avec précision décimale fixe. La valeur minimale est -92 233 720 368 547 758.08 | La valeur maximale est +92 233 720 368 547 758.07.
Date et heure DATE - Valeurs avec une partie date, mais pas de partie heure.
 : la plage acceptée est comprise entre 4713 av. J.-C. et 5 874 897 apr. J.-C, avec une résolution d'un jour.
TIMESTAMP (p) - Valeurs contenant des parties date et heure.
 : la plage acceptée est comprise entre 4713 av. J.-C. et 294 276 apr. J.-C, avec une résolution d'un jour.
 : la résolution est de p chiffres décimaux en secondes (ou d'une microseconde si elle n'est pas spécifiée).
TIMESTAMP (p) with time zone - Valeurs contenant à la fois la date, l'heure et le fuseau horaire.
 : la plage acceptée est comprise entre 4713 av. J.-C. et 294 276 apr. J.-C, avec une résolution d'un jour.
 : la résolution est de p chiffres décimaux en secondes (ou d'une microseconde si elle n'est pas spécifiée).
TIME (p) - Valeurs contenant une partie heure, mais pas de partie date.
 : la plage acceptée est comprise entre 00:00:00 et 24:00:00.
 : la résolution est de p chiffres décimaux en secondes (ou d'une microseconde si elle n'est pas spécifiée).
TIME (p) with time zone - Valeurs comportant une heure et un fuseau horaire, mais pas de partie date.
 : la plage acceptée est comprise entre 00:00:00 et 24:00:00.
 : la résolution est de p chiffres décimaux en secondes (ou d'une microseconde si elle n'est pas spécifiée).
INTERVAL (p) - Intervalle de temps
- La plage acceptée est comprise entre -178 000 000 et 178 000 000 ans.
 : la résolution est de p chiffres décimaux en secondes (ou d'une microseconde si elle n'est pas spécifiée).
JSON JSON Données JSON textuelles.
JSONB Données JSON binaires.
XML XML Données XML.

Géométrie		 GEOMETRY Type de colonne à spécifier lorsque vous souhaitez utiliser les modèles de données suivants.
POINT Une valeur (x,y).
LINE Un tuple (A, B, C) dans lequel Ax + By + C = 0 et A et B sont tous deux non nuls.

OU

Point 1 et point 2 aux formats suivants :

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
LSEG Un segment de ligne ((x1,y1),(x2,y2)).
BOX Cadre rectangulaire ((x1,y1),(x2,y2)).
PATH Une séquence de points ((x1,y1),...).
POLYGON Une séquence de points qui forme un tracé fermé.
CIRCLE (x,y),r) (point central et rayon)
Logique BOOLEAN - Contient une valeur "true", "false" ou "null".
- Accepte des valeurs telles que yes, true, t, on et 1 comme "true".
Bit / données binaires BIT (n) Chaîne de bits de longueur fixe.
BYTEA Données binaires / tableau d'octets.
Types liés aux adresses réseau CIDER Réseaux IPv4 et IPv6.
INET Hôtes et réseaux IPv4 et IPv6.
MACADDR Adresses MAC.
MACADDR8 Adresses MAC (format EUI-64).
Others PG_LSN Numéro de séquence de journal Cloud SQL pour PostgreSQL.
TSQUERY Requête de recherche de texte.
TSVECTOR Document de recherche de texte.
TXID_SNAPSHOT Instantané d'ID de transaction au niveau utilisateur.
UUID identifiant universel unique.

Conversion de types de données Oracle vers Cloud SQL pour PostgreSQL

Famille de types de données Oracle Nom du type de données Oracle Spécification du type de données Oracle Équivalent Cloud SQL pour PostgreSQL vers Oracle Type correspondant/alternatif Cloud SQL pour PostgreSQL
Chaîne/caractère CHAR(n) Taille maximale de 2 000 octets. Oui CHAR(n)
CHARACTER(n) Taille maximale de 2 000 octets. Oui CHARACTER(n)
NCHAR(n) Taille maximale de 2 000 octets. Non CHAR(n)
VARCHAR(n) Taille maximale de 2 000 octets. Oui VARCHAR(n)
NCHAR VARYING(n) Chaîne UTF-8 à longueur variable. Taille maximale
de 4 000 octets.		 Non CHARACTER VARYING(n)
VARCHAR2(n) 11g Taille maximale de 4 000 octets. Taille maximale de 32 Ko en PL/SQL. Oui* VARCHAR(n)
VARCHAR2(n)
12g		 Taille maximale de 32 767 octets MAX_STRING_SIZE= EXTENDED. Oui* VARCHAR(n)
NVARCHAR2(n) Taille maximale de 4 000 octets. Non VARCHAR(n)
LONG Taille maximale de 2 Go. Oui* TEXT
RAW(n) Taille maximale de 2 000 octets. Oui* BYTEA
LONG RAW Taille maximale de 2 Go. Oui* BYTEA
Numérique NUMBER Nombre à virgule flottante. Oui* NUMERIC(p,s)
NUMBER(*) Nombre à virgule flottante. Oui* DOUBLE PRECISION
NUMERIC(p,s) La précision peut aller de 1 à 38. Oui NUMERIC(p,s)
FLOAT(p,s) Nombre à virgule flottante. Oui* DOUBLE PRECISION
DEC(p,s) Nombre à virgule fixe. Oui DEC(p,s)
DECIMAL(p,s) Nombre à virgule fixe. Oui DECIMAL(p,s)
INT Entier de 38 chiffres. Oui INT
INTEGER Entier de 38 chiffres. Oui INTEGER
SMALLINT Entier de 38 chiffres. Oui SMALLINT
REAL Nombre à virgule flottante. Oui* DOUBLE PRECISION
DOUBLE PRECISION Nombre à virgule flottante. Oui DOUBLE PRECISION
Date et heure DATE Stocke les données de date et d'heure (année, mois, jour, heure, minute et seconde). Oui* TIMESTAMP(0)
TIMESTAMP(p) Date et heure avec fraction. Oui TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE Date et heure avec fraction et fuseau horaire. Oui TIMESTAMP (p) WITH TIME ZONE
INTERVAL YEAR(p) TO MONTH Intervalle de date. Oui* INTERVAL YEAR TO MONTH
INTERVAL DAY(p) TO SECOND(s) Intervalle de jour et d'heure. Oui* INTERVAL DAY TO SECOND(s)
Logique BOOLEAN Valeurs TRUE, FALSE et NULL. Ne peuvent pas être attribuées à une colonne de table de base de données. Oui BOOLEAN
XML XMLTYPE Données XML. Non XML
LOB BFILE Pointeur vers un fichier binaire, d'une taille maximale de 4 Go. Non VARCHAR(255)
CLOB Objet volumineux à base de caractères avec une taille de fichier maximale de 4 Go. Non TEXT
BLOB Objet binaire volumineux d'une taille maximale de 4 Go. Non BYTEA
NCLOB Chaîne Unicode de longueur variable avec une taille de fichier maximale de 4 Go. Non TEXT
ROWID ROWID Adresse de ligne physique. Non CTID
UROWID(n) ID de ligne universelle des adresses de ligne logique. Non CTID
Spatial SDO_ GEOMETRY Description géométrique d'un objet spatial. Oui Postgres utilise différents types de géométrie, y compris point, line, path, polygon, circle, etc.
SDO_TOPO_ GEOMETRY Décrit une géométrie de topologie. Oui PostGIS, une extension Postgres couramment utilisée, fournit plusieurs types de topologie.
SDO_GEORASTER Une grille matricielle ou un objet image est stocké sur une seule ligne. Oui PostGIS, une extension Postgres couramment utilisée, fournit un outil permettant de charger des fichiers raster dans la base de données.
Types de contenu ORDDicom Compatible avec le stockage et la gestion des fichiers de données au format DICOM (Digital Imaging and Communications in Medicine). Oui, avec solution de contournement Postgres fournit un type binaire, bytea, pour le stockage d'objets volumineux.
ORDDoc Compatible avec le stockage et la gestion de tout type de données multimédias. Oui, avec solution de contournement Postgres fournit un type binaire, bytea, pour le stockage d'objets volumineux.
ORDImage Compatible avec le stockage et la gestion des données d'image. Oui, avec solution de contournement Postgres fournit un type binaire, bytea, pour le stockage d'objets volumineux.
ORDVideo Compatible avec le stockage et la gestion des données vidéo. Solution Postgres fournit un type binaire, bytea, pour le stockage d'objets volumineux.

* Le type de données correspondant dans Cloud SQL pour PostgreSQL est essentiellement compatible avec Oracle, mais porte un nom différent.

Types définis par l'utilisateur

Oracle fait référence aux types définis par l'utilisateur (UDT) en tant que OBJECT TYPES, qui sont gérés à l'aide de PL/SQL. Les types définis par l'utilisateur permettent à celui-ci de créer des types de données complexes, dédiés aux applications et basés sur la liste de types de données Oracle intégrés.

Types Oracle définis par l'utilisateur Présentation ou mise en œuvre dans Oracle Compatibilité avec Cloud SQL pour PostgreSQL Solution Cloud SQL pour PostgreSQL correspondante ou alternative
Type de données abstrait (Abstract data type, ADT) CREATE TYPE ADT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
);		 Partielle, avec une syntaxe différente Les types composites de Cloud SQL pour PostgreSQL sont spécifiés par une liste de noms d'attributs et de types de données :

CREATE TYPE ADT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
Type ARRAY CREATE TYPE VARRAY_DEMO AS VARRAY (5) OF VARCHAR2(25); Non Pour contourner ce problème, Cloud SQL pour PostgreSQL permet de définir des colonnes en tant que tableau :

CREATE TABLE VARRAY_TABLE (
NAME VARCHAR(25)[]
);
Type de table imbriquée CREATE TYPE NTT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
) ;
CREATE TYPE NTT_TABLE AS TABLE OF NTT_DEMO;		 Non Pour contourner le problème, utilisez une combinaison de types composites et de définition de colonne de tableau dans Cloud SQL pour PostgreSQL, afin d'obtenir des fonctionnalités semblable au type de table imbriquée d'Oracle.

CREATE TYPE NTT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
CREATE TABLE NTT_TABLE (
RECORD NTT_DEMO[]
);
Type incomplet Les types incomplets sont des types sans attributs ni méthodes. Ils peuvent être référencés par d'autres types. Cependant, vous devez fournir la définition du type avant de l'utiliser.

CREATE TYPE INCOMPLETE_DEMO;

 Non N/A

Utilisateurs

Cette section explique comment créer des utilisateurs et leur attribuer des autorisations, ainsi que la nécessité de convertir des tables Oracle en tables Cloud SQL pour PostgreSQL.

Création d'utilisateurs et autorisations

Les comptes utilisateur de la base de données Oracle ("user" et "schema" sont identiques dans Oracle) peuvent être utilisés pour s'authentifier et se connecter aux sessions de base de données, tandis que l'autorisation d'accès est définie au niveau de chaque utilisateur pour des objets et des autorisations spécifiques.

En général, il existe deux types d'utilisateurs de base de données :

  • Administrateurs : gestion de l'instance de base de données, des utilisateurs et des ressources.
  • Comptes utilisateur : diffusion d'opérations logiques telles que des applications.

Les administrateurs accordent des droits aux comptes utilisateur et d'application pour accéder aux objets de la base de données. Les autorisations de base de données Oracle sont accordées à un utilisateur pour l'accès à des opérations spécifiques (par exemple, créer une session/connexion) ou à des objets de base de données spécifiques (par exemple, SELECT sur une table spécifique ou EXECUTE sur une procédure stockée spécifique).

Oracle utilise les concepts d'utilisateur et de rôle. Les utilisateurs permettent de s'authentifier auprès de la base de données, et les rôles fournissent un regroupement d'autorisations pouvant être accordées de manière globale.

Dans Cloud SQL pour PostgreSQL, les utilisateurs et les rôles sont synonymes. Un utilisateur Cloud SQL pour PostgreSQL est un rôle disposant de l'autorisation connect. Vous pouvez utiliser les instructions CREATE USER ou CREATE ROLE pour créer un utilisateur de base de données. Contrairement à Oracle, dans Cloud SQL pour PostgreSQL, les schémas et les utilisateurs sont créés séparément. Un schéma est une collection d'objets (par exemple, des tables, des types de données, des fonctions, etc.) et appartient à un utilisateur.

Créer un utilisateur Oracle
CREATE USER user_name IDENTIFIED BY password;
Créer un utilisateur Cloud SQL pour PostgreSQL
CREATE USER user WITH PASSWORD 'password';

OU

CREATE ROLE user WITH LOGIN PASSWORD 'password';

Remarques sur les conversions

  • Les utilisateurs sont définis au niveau racine et sont valables pour toutes les bases de données contenues dans le même cluster Cloud SQL pour PostgreSQL. Ce système est semblable aux "utilisateurs communs" d'Oracle 12c.
  • La syntaxe CREATE USER de Cloud SQL pour PostgreSQL est différente de celle d'Oracle et ne peut pas être migrée telle quelle. CREATE USER dans Cloud SQL pour PostgreSQL est un alias pour CREATE ROLE, mais avec l'option LOGIN activée par défaut.

Tables

Les tables Oracle sont construites à partir de nombreux éléments tels que les types de données de colonne, les contraintes de table, les index, les partitions, les fonctionnalités propriétaires des tables Oracle, etc. Pour réussir la migration vers les tables de base de données Cloud SQL pour PostgreSQL, tous les éléments de table Oracle doivent être convertis en tables PostgreSQL. Certains éléments sont compatibles avec pas ou peu de modifications, tandis que d'autres doivent être entièrement modifiés.

Du point de vue de la migration, la conversion de PL/SQL en Cloud SQL for PostgreSQL nécessite probablement davantage d'efforts, car la conversion de tables Oracle en tables Cloud SQL for PostgreSQL est une étape essentielle qui a une importance significative et des conséquences sur les performances et la taille des données.

Voici les principales différences entre les tables Oracle et Cloud SQL pour PostgreSQL et les fonctionnalités associées. Ces différences sont abordées dans les autres parties de la série.

  • Syntaxe de création de table
  • Métadonnées de table et d'index
  • Compatibilité avec les contraintes
  • Types de données acceptés et limites
  • Index
  • Partitions et gestion des partitions
  • Gestion des tables et des index
  • Tables temporaires
  • Vues
  • Colonnes visibles et invisibles (Oracle 12c)
  • Jeux de caractères des tables et des colonnes

Étape suivante