Migrar utilizadores do Oracle® para o Cloud SQL para PostgreSQL: tipos de dados, utilizadores e tabelas

Este documento faz parte de uma série que fornece informações e orientações importantes relacionadas com o planeamento e a execução de migrações de bases de dados Oracle® 11g/12c para o Cloud SQL para PostgreSQL versão 12. Além da parte de configuração introdutória, a série inclui as seguintes partes:

Tipos de dados

O Cloud SQL para PostgreSQL oferece vários tipos de dados que são totalmente equivalentes ou semelhantes aos tipos de dados fornecidos pela Oracle. A tabela seguinte apresenta os tipos de dados do PostgreSQL mais comuns, seguida de uma comparação entre os tipos de dados primitivos do Oracle e os tipos de dados correspondentes do Cloud SQL para PostgreSQL. Quando um tipo de dados não é suportado, é apresentado um tipo de dados alternativo.

Tipos de dados primitivos do Cloud SQL para PostgreSQL 12

Família de tipos de dados do Cloud SQL para PostgreSQL Nome do tipo de dados do Cloud SQL para PostgreSQL Especificação do tipo de dados
String/caráter CHAR(n) Armazena exatamente n carateres. Alias de CHARACTER(n).
CHARACTER(n) Armazena exatamente n carateres.
VARCHAR(n) Armazena um número variável de carateres, até um máximo de n carateres. Alias de CHARACTER VARYING(n).
CHARACTER VARYING(n) Armazena um número variável de carateres, até um máximo de n carateres.
TEXT Variante específica de VARCHAR que não requer que especifique um limite superior para o número de carateres. A string de carateres mais longa possível que pode ser armazenada é de 1 GB (o mesmo para todos os tipos de dados de carateres de string).
Numérico SMALLINT O valor mínimo é -32768 e o valor máximo é 32767.
INTEGER O valor mínimo é -2147483648 e o valor máximo é 2147483647.
BIGINT O valor mínimo é -2^63 e o valor máximo é 2^63-1.
REAL Número de vírgula flutuante de precisão simples (4 bytes).
DOUBLE PRECISION Número de vírgula flutuante de precisão dupla (8 bytes).
DECIMAL (p,s) Capaz de armazenar qualquer valor com p dígitos e s decimais.
NUMERIC(p,s) Capaz de armazenar qualquer valor com p dígitos e s decimais.
SMALLSERIAL Número inteiro de incremento automático. O valor mínimo é 1 e o valor máximo é 32767.
SERIAL Número inteiro de incremento automático. O valor mínimo é 1 e o valor máximo é 2147483647.
BIGSERIAL Número inteiro de incremento automático. O valor mínimo é 1 | O valor máximo é 2^63-1.
Monetário MONEY Valor da moeda com uma precisão fracionária fixa. O valor mínimo é -92233720368547758,08 | O valor máximo é +92233720368547758,07.
Data e hora DATE – Valores com uma parte de data, mas sem parte de hora.
- O intervalo suportado é de 4713 a.C. a 5874897 d.C. com uma resolução de 1 dia.
TIMESTAMP (p) – Valores que contêm partes de data e hora.
- O intervalo suportado é de 4713 a.C. a 294276 d.C.
- A resolução é de p dígitos decimais em segundos (ou 1 microssegundo se não for especificado).
TIMESTAMP (p) with time zone – Valores que contêm a data, a hora e o fuso horário.
- O intervalo suportado é de 4713 a.C. a 294276 d.C.
- A resolução é de p dígitos decimais em segundos (ou 1 microssegundo se não for especificado).
TIME (p) – Valores com uma parte de tempo, mas sem uma parte de data.
- O intervalo suportado é de 00:00:00 a 24:00:00.
- A resolução é de p dígitos decimais em segundos (ou 1 microssegundo se não for especificado).
TIME (p) with time zone – Valores com uma hora e um fuso horário, mas sem parte de data.
- O intervalo suportado é de 00:00:00 a 24:00:00.
- A resolução é de p dígitos decimais em segundos (ou 1 microssegundo se não for especificado).
INTERVAL (p) – Intervalo de tempo
– O intervalo suportado é de -178 000 000 a 178 000 000 anos.
- A resolução é de p dígitos decimais em segundos (ou 1 microssegundo se não for especificado).
JSON JSON Dados JSON textuais.
JSONB Dados JSON binários.
XML XML Dados XML.

Geométrico		 GEOMETRY O tipo de coluna para especificar quando quer usar os seguintes modelos de dados.
POINT Um valor (x,y).
LINE Uma tupla (A, B, C) em que Ax + By + C = 0 e A e B não são ambos 0.

OU

Ponto 1 e ponto 2 nos seguintes formatos:

[ ( x1 , y1 ) , ( x2 , y2 ) ]
( ( x1 , y1 ) , ( x2 , y2 ) )
( x1 , y1 ) , ( x2 , y2 )
x1 , y1 , x2 , y2
LSEG Um segmento de linha ((x1,y1),(x2,y2)).
BOX Caixa retangular ((x1,y1),(x2,y2)).
PATH Uma sequência de pontos ((x1,y1),...).
POLYGON Uma sequência de pontos, efetivamente um caminho fechado.
CIRCLE (x,y),r) (ponto central e raio)
Lógico BOOLEAN – Contém um valor verdadeiro, falso ou nulo.
- Aceita valores como yes, true, t, on e 1 como verdadeiros.
Dados binários / de bits BIT (n) String de bits de comprimento fixo.
BYTEA Dados binários / matriz de bytes.
Tipos de endereços de rede CIDER Redes IPv4 e IPv6.
INET Anfitriões e redes IPv4 e IPv6.
MACADDR Endereços MAC.
MACADDR8 Endereços MAC (formato EUI-64).
Outros PG_LSN Número de sequência do registo do Cloud SQL para PostgreSQL.
TSQUERY Consulta de pesquisa de texto.
TSVECTOR Pesquisar texto no documento.
TXID_SNAPSHOT Resumo do ID da transação ao nível do utilizador.
UUID Identificador universalmente exclusivo.

Conversão de tipo de dados do Oracle para o Cloud SQL para PostgreSQL

Família de tipos de dados Oracle Nome do tipo de dados Oracle Especificação do tipo de dados da Oracle Cloud SQL para PostgreSQL para o equivalente do Oracle Cloud SQL para PostgreSQL correspondente/alternativo
String/caráter CHAR(n) Tamanho máximo de 2000 bytes. Sim CHAR(n)
CHARACTER(n) Tamanho máximo de 2000 bytes. Sim CHARACTER(n)
NCHAR(n) Tamanho máximo de 2000 bytes. Não CHAR(n)
VARCHAR(n) Tamanho máximo de 2000 bytes. Sim VARCHAR(n)
NCHAR VARYING(n) String UTF-8 de comprimento variável. Tamanho máximo
de 4000 bytes.		 Não CHARACTER VARYING(n)
VARCHAR2(n) 11g Tamanho máximo de 4000 bytes e tamanho máximo de 32 KB em PL/SQL. Sim* VARCHAR(n)
VARCHAR2(n)
12g		 Tamanho máximo de 32 767 bytes MAX_STRING_SIZE= EXTENDED. Sim* VARCHAR(n)
NVARCHAR2(n) Tamanho máximo de 4000 bytes. Não VARCHAR(n)
LONG O tamanho máximo é de 2 GB. Sim* TEXT
RAW(n) Tamanho máximo de 2000 bytes. Sim* BYTEA
LONG RAW O tamanho máximo é de 2 GB. Sim* BYTEA
Numérico NUMBER Número de vírgula flutuante. Sim* NUMERIC(p,s)
NUMBER(*) Número de vírgula flutuante. Sim* DOUBLE PRECISION
NUMERIC(p,s) A precisão pode variar entre 1 e 38. Sim NUMERIC(p,s)
FLOAT(p,s) Número de vírgula flutuante. Sim* DOUBLE PRECISION
DEC(p,s) Número de ponto fixo. Sim DEC(p,s)
DECIMAL(p,s) Número de ponto fixo. Sim DECIMAL(p,s)
INT Número inteiro de 38 dígitos. Sim INT
INTEGER Número inteiro de 38 dígitos. Sim INTEGER
SMALLINT Número inteiro de 38 dígitos. Sim SMALLINT
REAL Número de vírgula flutuante. Sim* DOUBLE PRECISION
DOUBLE PRECISION Número de vírgula flutuante. Sim DOUBLE PRECISION
Data e hora DATE Armazena dados de data e hora (ano, mês, dia, hora, minuto e segundo). Sim* TIMESTAMP(0)
TIMESTAMP(p) Data e hora com fração. Sim TIMESTAMP(p)
TIMESTAMP(p) WITH TIME ZONE Data e hora com fração e fuso horário. Sim TIMESTAMP (p) WITH TIME ZONE
INTERVAL YEAR(p) TO MONTH Intervalo de datas. Sim* INTERVAL YEAR TO MONTH
INTERVAL DAY(p) TO SECOND(s) Intervalo de dia e hora. Sim* INTERVAL DAY TO SECOND(s)
Lógico BOOLEAN Valores TRUE, FALSE e NULL. Não pode ser atribuído a uma coluna de tabela de base de dados. Sim BOOLEAN
XML XMLTYPE Dados XML. Não XML
LOB BFILE Ponteiro para o ficheiro binário, com um tamanho máximo de 4 GB. Não VARCHAR(255)
CLOB Objeto grande de carateres com um tamanho máximo de 4 GB. Não TEXT
BLOB Objeto binário grande com um tamanho máximo de 4 GB. Não BYTEA
NCLOB String Unicode de comprimento variável com um tamanho máximo de ficheiro de 4 GB. Não TEXT
ROWID ROWID Morada física da linha. Não CTID
UROWID(n) ID de linha universal dos endereços de linhas lógicas. Não CTID
Espacial SDO_ GEOMETRY A descrição geométrica de um objeto espacial. Sim O Postgres tem vários tipos de geometria, incluindo point, line, path, polygon, circle, ....
SDO_TOPO_ GEOMETRY Descreve uma geometria de topologia. Sim O PostGIS, uma extensão do Postgres amplamente usada, oferece vários tipos de topologia.
SDO_GEORASTER Uma grelha raster ou um objeto de imagem é armazenado numa única linha. Sim O PostGIS, uma extensão do Postgres amplamente usada, fornece uma ferramenta para carregar ficheiros raster para a base de dados.
Tipos de suportes ORDDicom Suporta o armazenamento e a gestão de ficheiros de imagens digitais e comunicações em medicina (DICOM). Sim, com uma solução alternativa O Postgres oferece um tipo binário, bytea, para armazenar objetos grandes.
ORDDoc Suporta o armazenamento e a gestão de qualquer tipo de dados multimédia. Sim, com uma solução alternativa O Postgres oferece um tipo binário, bytea, para armazenar objetos grandes.
ORDImage Suporta o armazenamento e a gestão de dados de imagens. Sim, com uma solução alternativa O Postgres oferece um tipo binário, bytea, para armazenar objetos grandes.
ORDVideo Suporta o armazenamento e a gestão de dados de vídeo. Alternativa O Postgres oferece um tipo binário, bytea, para armazenar objetos grandes.

* O tipo de dados correspondente no Cloud SQL para PostgreSQL é maioritariamente compatível com o Oracle, mas tem um nome diferente.

Tipos definidos pelo utilizador

A Oracle refere-se aos tipos definidos pelo utilizador (UDTs) como OBJECT TYPES, que são geridos através do PL/SQL. Os tipos definidos pelo utilizador permitem que o utilizador crie tipos de dados complexos dedicados à aplicação, que se baseiam e se expandem a partir da lista de tipos de dados incorporados do Oracle.

Tipos definidos pelo utilizador da Oracle Vista geral ou implementação da Oracle Suporte do Cloud SQL para PostgreSQL Solução correspondente ou alternativa do Cloud SQL para PostgreSQL
Tipo de dados abstrato (TDA) CREATE TYPE ADT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
);		 Parcialmente com uma sintaxe diferente Os tipos compostos do Cloud SQL para PostgreSQL são especificados por uma lista de nomes de atributos e tipos de dados:

CREATE TYPE ADT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
Tipo VARRAY CREATE TYPE VARRAY_DEMO AS VARRAY (5) OF VARCHAR2(25); Não Como solução alternativa, o Cloud SQL para PostgreSQL permite que as colunas sejam definidas como matriz:

CREATE TABLE VARRAY_TABLE (
NAME VARCHAR(25)[]
);
Tipo de tabela aninhada CREATE TYPE NTT_DEMO AS OBJECT (
ID NUMBER(6),
NAME VARCHAR2(20)
) ;
CREATE TYPE NTT_TABLE AS TABLE OF NTT_DEMO;		 Não Como alternativa, use uma combinação de tipos compostos e definição de coluna de matriz no Cloud SQL para PostgreSQL para alcançar uma funcionalidade semelhante à do tipo de tabela aninhada da Oracle.

CREATE TYPE NTT_DEMO AS
(
ID NUMERIC(6),
NAME VARCHAR(20)
);
CREATE TABLE NTT_TABLE (
RECORD NTT_DEMO[]
);
Tipo incompleto Os tipos incompletos são tipos sem atributos nem métodos. Podem ser referenciados por outros tipos. No entanto, tem de fornecer a definição do tipo antes de a usar.

CREATE TYPE INCOMPLETE_DEMO;

 Não N/A

Utilizadores

Esta secção aborda a criação de utilizadores e a atribuição de autorizações, bem como a necessidade de converter tabelas Oracle em tabelas do Cloud SQL para PostgreSQL.

Criação e autorizações de utilizadores

As contas de utilizador da base de dados Oracle (o "utilizador" e o "esquema" da Oracle são idênticos) podem ser usadas para autenticar e estabelecer ligação a sessões da base de dados, enquanto o acesso de autorização é definido ao nível individual do utilizador para objetos/autorizações específicos da base de dados.

Em geral, existem dois tipos de utilizadores da base de dados:

  • Administradores: gerir a instância da base de dados, os utilizadores e os recursos.
  • Contas de utilizador: publicação de operações lógicas, como aplicações.

Os administradores concedem privilégios a contas de utilizador e de utilizador da aplicação para aceder aos objetos da base de dados. As autorizações da base de dados Oracle são concedidas a um utilizador para operações específicas (por exemplo, criar sessão/ligar) ou objetos de base de dados específicos (por exemplo, SELECT numa tabela específica ou EXECUTE num procedimento armazenado específico).

No Oracle, existe o conceito de utilizadores e funções. Os utilizadores são usados para autenticar com a base de dados e as funções fornecem o agrupamento de autorizações que podem ser concedidas na totalidade.

No Cloud SQL para PostgreSQL, os utilizadores e as funções são sinónimos. Um utilizador no Cloud SQL para PostgreSQL é uma função com a autorização connect. Pode usar a declaração CREATE USER ou CREATE ROLE para criar um utilizador da base de dados. Ao contrário do Oracle, no Cloud SQL para PostgreSQL, os esquemas e os utilizadores são criados separadamente. Um esquema é uma coleção de objetos (por exemplo, tabelas, tipos de dados, funções, etc.) e é propriedade de um utilizador.

Oracle Create User
CREATE USER user_name IDENTIFIED BY password;
Cloud SQL para PostgreSQL Create User
CREATE USER user WITH PASSWORD 'password';

OU

CREATE ROLE user WITH LOGIN PASSWORD 'password';

Considerações sobre as conversões

  • Os utilizadores são definidos ao nível da raiz e são válidos para todas as bases de dados contidas no mesmo cluster do Cloud SQL para PostgreSQL. Isto é semelhante aos "utilizadores comuns" no Oracle 12c.
  • A sintaxe CREATE USER do Cloud SQL para PostgreSQL é diferente da do Oracle e não pode ser migrada tal como está. CREATE USER no Cloud SQL para PostgreSQL é um alias de CREATE ROLE, exceto que a opção LOGIN está ativada por predefinição.

Tabelas

As tabelas Oracle são criadas a partir de muitos elementos, como tipos de dados de colunas, restrições de tabelas, índices, partições, funcionalidades proprietárias de tabelas Oracle e muito mais. Para migrar com êxito para tabelas de base de dados do Cloud SQL para PostgreSQL, todos os elementos das tabelas do Oracle têm de ser convertidos em tabelas do Cloud SQL para PostgreSQL. Alguns elementos são suportados sem modificações ou com modificações menores, enquanto outros têm de ser modificados completamente.

Do ponto de vista da migração, a conversão de PL/SQL em Cloud SQL para PostgreSQL requer provavelmente mais esforço, uma vez que a conversão de tabelas Oracle em tabelas Cloud SQL para PostgreSQL é uma fase crucial com importância significativa e implicações adicionais no desempenho e no tamanho dos dados.

Seguem-se as principais diferenças entre as tabelas do Oracle e do Cloud SQL for PostgreSQL, bem como as funcionalidades relacionadas. Estas diferenças são abordadas nas restantes partes da série.

  • Sintaxe de criação de tabelas
  • Metadados de tabelas e índices
  • Compatibilidade com restrições
  • Suporte e limitações de tipos de dados
  • Índices
  • Partições e gestão de partições
  • Manutenção de tabelas e índices
  • Tabelas temporárias
  • Visualizações
  • Colunas visíveis e invisíveis (Oracle 12c)
  • Conjuntos de carateres de tabelas e colunas

O que se segue?