本頁面由 Cloud Translation API 翻譯而成。

將 Oracle® 資料庫使用者和結構定義遷移至 PostgreSQL 適用的 Cloud SQL

本文件是一系列文件的一部分，提供有關規劃及執行 Oracle® 11g/12c 資料庫遷移至 PostgreSQL 適用的 Cloud SQL 12 版的相關重要資訊和指引。本文將說明 Oracle® Database 和 PostgreSQL 適用的 Cloud SQL 在建立使用者、結構定義、資料表、索引和檢視畫面時的基本差異。

除了初步設定部分外，本系列還包含以下部分：

將 Oracle 使用者遷移至 PostgreSQL 適用的 Cloud SQL：術語和功能
將 Oracle 使用者遷移至 PostgreSQL 適用的 Cloud SQL：資料類型、使用者和資料表
將 Oracle 使用者遷移至 PostgreSQL 適用的 Cloud SQL：查詢、預存程序、函式和觸發事件
將 Oracle 使用者遷移至 PostgreSQL 適用的 Cloud SQL：安全性、作業、監控和記錄
將 Oracle 資料庫使用者和結構定義遷移至 PostgreSQL 適用的 Cloud SQL (本文件)

Oracle 和 PostgreSQL 適用的 Cloud SQL 之間的用語差異

Oracle 和 PostgreSQL 適用的 Cloud SQL 在執行個體、資料庫、使用者和結構定義方面，採用不同的架構和術語。如需這些差異的摘要，請參閱本系列的術語部分。

匯出 Oracle 設定

規劃遷移至 PostgreSQL 適用的 Cloud SQL 時，第一步就是檢查來源 Oracle 資料庫的現有參數設定。記憶體配置、字元集和儲存空間參數的相關設定特別實用，因為這些設定可提供 Cloud SQL for PostgreSQL 目標環境的初始設定和大小。擷取 Oracle 參數設定的方法有很多種。以下列舉幾個常見的錯誤：

自動工作負載存放區 (AWR) 報表會保留資源分配資料 (CPU、RAM)、執行個體參數設定和最大活動工作階段。
DBA_HIST、V$OSSTAT 和 V$LICENSE 可提供 CPU 使用率詳細資料。
V$PARAMETER 檢視資料庫設定參數。
V$NLS_PARAMETERS 檢視資料庫語言參數。
DBA_DATA_FILES 檢視畫面，用於計算資料庫儲存空間大小。
資料庫執行個體設定的 Oracle SPFILE。
工作排程工具 (例如 crontab)，用於識別應考慮的例行備份或維護期間。

在 PostgreSQL 適用的 Cloud SQL 中匯入及設定使用者

從高層面來看，每個 Oracle 結構定義都應在 PostgresQL 中建立為各自的結構定義。在 Oracle 資料庫中，「使用者」與「結構定義」是同義詞。也就是說，您建立使用者時會建立結構定義。使用者和結構定義之間的關係一律為 1:1。在 PostgreSQL 中，使用者和結構定義是分開建立的。您可以建立使用者，而無須建立相對應的結構定義。如要在 PostgreSQL 中維持相同的 Oracle 使用者或結構定義，您可以為每位使用者建立結構定義。

下表列出轉換示例：

動作類型	資料庫類型	指令比較
建立使用者和結構定義	`Oracle`	`CREATE USER username IDENTIFIED BY password;`
	`PostgreSQL`	使用者和結構定義在 PostgreSQL 中是不同的概念，因此需要兩個不同的 `CREATE` 陳述式 `CREATE USER username WITH PASSWORD 'password'; CREATE SCHEMA schema_name;`
指派角色	`Oracle`	`GRANT CONNECT TO username;`
	`PostgreSQL`	`GRANT pg_monitor TO username;`
授予權限	`Oracle`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
	`PostgreSQL`	`GRANT SELECT, INSERT, UPDATE ON HR.EMPLOYEES TO username;`
撤銷權限	`Oracle`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
	`PostgreSQL`	`REVOKE UPDATE ON HR.EMPLOYEES FROM username;`
授予資料庫管理員/超級使用者	`Oracle`	`GRANT DBA TO username;`
	`PostgreSQL`	`GRANT cloudsqlsuperuser TO username;`
刪除使用者	`Oracle`	`DROP USER username CASCADE;`
	`PostgreSQL`	使用者和結構定義在 PostgreSQL 中是不同的概念，因此需要兩個不同的 `DROP` 陳述式 `DROP USER username; DROP SCHEMA schema_name CASCADE;`
使用者中繼資料	`Oracle`	`DBA_USERS`
	`PostgreSQL`	`pg_catalog.pg_user`
權限中繼資料	`Oracle`	`DBA_SYS_PRIVS DBA_ROLE_PRIVS SESSION_PRIVS`
	`PostgreSQL`	`pg_catalog.pg_roles`
CLI 連線字串	`Oracle`	`sqlplus username/password@host/tns_alias Sqlplus username/password@host:IP/sid`
	`PostgreSQL`	沒有密碼提示： `PGPASSWORD=password psql -h hostname -U username -d database_name` 有密碼提示： `psql -h hostname -U username -W -d database_name`

Oracle 12c 資料庫使用者：

Oracle 12c 有兩種使用者：一般使用者和本機使用者。常見使用者會在根 CDB 中建立，包括 PDB。這些執行個體的使用者名稱會加上 C## 前置字串，本機使用者只能在特定 PDB 中建立。您可以在多個 PDB 中建立具有相同使用者名稱的不同資料庫使用者。從 Oracle 12c 遷移至 PostgreSQL 時，請修改使用者和權限，以符合 PostgreSQL 的架構。以下是兩個常見的範例，說明這些差異：

# Oracle local user
SQL> ALTER SESSION SET CONTAINER=pdb;
SQL> CREATE USER username IDENTIFIED BY password QUOTA 50M ON USERS;

# PostgreSQL user for a single database and schema
postgres=> CREATE USER username WITH PASSWORD 'password';
postgres=> GRANT CONNECT TO DATABASE database_name TO username;
postgres=> GRANT USAGE ON SCHEMA schema_name TO username;
postgres=> -- Optionally, grant object privileges in the schema
postgres=> GRANT ALL ON ALL TABLES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL FUNCTIONS IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL PROCEDURES IN SCHEMA schema_name TO username;
postgres=> GRANT ALL ON ALL ROUTINES IN SCHEMA schema_name TO username;

# Oracle common user
SQL> CREATE USER c##username IDENTIFIED BY password CONTAINER=ALL;

# PostgreSQL user with permissions for all database (use the local user script above and repeat it for each database and schema)

透過 Google Cloud 控制台管理使用者

如要查看目前已設定的 PostgreSQL 適用的 Cloud SQL 使用者，請前往 Google Cloud 控制台的以下頁面：

Google Cloud > 儲存空間 > SQL > 執行個體 > 使用者

「使用者」頁面的螢幕截圖。

匯入資料表和檢視表定義

Oracle 和 PostgreSQL 在區分大小寫方面有所不同。Oracle 名稱不區分大小寫。PostgreSQL 名稱不區分大小寫，除非以雙引號括住。許多 Oracle 的結構定義匯出和 SQL 產生工具 (例如 DBMS_METADATA.GET_DDL) 會自動在物件名稱中加上雙引號。這些引號可能會在遷移後導致各種問題。建議您在 PostgreSQL 中建立物件之前，先從資料定義語言 (DDL) 陳述式中移除所有物件名稱的引號。

建立資料表語法

將資料表從 Oracle 轉換為 PostgreSQL 資料類型時，第一步是從來源資料庫中擷取 Oracle 建立資料表陳述式。以下範例查詢會從人力資源結構定義中擷取位置資料表的 DDL：

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'LOCATIONS') FROM DUAL;

CREATE TABLE "HR"."LOCATIONS"
   (  "LOCATION_ID" NUMBER(4,0),
  "STREET_ADDRESS" VARCHAR2(40),
  "POSTAL_CODE" VARCHAR2(12),
  "CITY" VARCHAR2(30) CONSTRAINT "LOC_CITY_NN" NOT NULL ENABLE,
  "STATE_PROVINCE" VARCHAR2(25),
  "COUNTRY_ID" CHAR(2),
  CONSTRAINT "LOC_ID_PK" PRIMARY KEY ("LOCATION_ID")
...
      CONSTRAINT "LOC_C_ID_FK" FOREIGN KEY ("COUNTRY_ID")
          REFERENCES "HR"."COUNTRIES" ("COUNTRY_ID") ENABLE

完整輸出內容包含儲存空間元素、索引和表格空間資訊，但這些項目已省略，因為 PostgreSQL CREATE TABLE 陳述式不支援這些額外元素。

擷取 DDL 後，請移除名稱周圍的引號，並根據 Oracle 到 PostgreSQL 資料類型的轉換表執行資料表轉換作業。檢查每個資料欄的資料類型，看看是否可以直接轉換，如果不支援，請根據轉換表選擇其他資料類型。例如，以下是位置資料表的轉換後 DDL。

CREATE TABLE HR.LOCATIONS (
  LOCATION_ID NUMERIC(4,0),
  STREET_ADDRESS VARCHAR(40),
  POSTAL_CODE VARCHAR(12),
  CITY VARCHAR(30) CONSTRAINT LOC_CITY_NN NOT NULL,
  STATE_PROVINCE VARCHAR(25),
  COUNTRY_ID CHAR(2),
  CONSTRAINT LOC_ID_PK PRIMARY KEY (LOCATION_ID),
  CONSTRAINT LOC_C_ID_FK FOREIGN KEY (COUNTRY_ID)
REFERENCES HR.COUNTRIES (COUNTRY_ID)
)

建立資料表做為選取 (CTAS)

CREATE TABLE AS SELECT (CTAS) 陳述式可用於根據現有資料表建立新資料表。請注意，系統只會複製資料欄名稱和資料欄資料類型，而不會複製限制和索引。PostgreSQL 支援 CTAS 功能的 ANSI SQL 標準，且與 Oracle CTAS 陳述式相容。

Oracle 12c 隱藏資料欄

PostgreSQL 不支援隱藏的資料欄。解決方法是建立只包含可見資料欄的檢視畫面。

資料表限制

Oracle 提供六種資料表限制，可在建立資料表時或使用 ALTER TABLE 指令建立資料表後定義。Oracle 限制條件類型為 PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL 和 REF。此外，Oracle 可讓使用者透過下列選項控制約束條件的狀態：

INITIALLY IMMEDIATE：檢查每個後續 SQL 陳述式結尾的限制條件 (預設狀態)。
DEFERRABLE/NOT DEFERRABLE：在提交 COMMIT 陳述式之前，允許在後續交易中使用 SET CONSTRAINT 子句
INITIALLY DEFERRED：在後續交易結束時檢查限制條件。
VALIDATE/NO VALIDATE：檢查 (或刻意不檢查) 新列或已修改的資料列是否有錯誤。這些參數取決於約束條件是 ENABLED 還是 DISABLED。
ENABLED/DISABLED：指定是否應在建立後強制執行限制 (預設為 ENABLED)

PostgreSQL 也支援六種資料表限制：PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、NOT NULL 和 EXCLUDE。不過，Oracle 和 PostgreSQL 的限制條件類型之間仍有一些明顯差異，包括：

PostgreSQL 不支援 Oracle 的 REF 限制條件。
PostgreSQL 不會自動為外鍵限制的參照欄建立索引。如果需要索引，則必須在參照資料欄上使用單獨的 CREATE INDEX 陳述式。
PostgreSQL 不支援 Oracle 的 ON DELETE SET NULL 子句。這個子句會指示 Oracle 在刪除父項資料表中的記錄時，將子項資料表中的任何從屬值設為 NULL。
系統不支援 VIEWS 上的限制條件，CHECK OPTION 除外。
PostgreSQL 不支援停用限制。使用 ALTER TABLE 陳述式新增外鍵或檢查限制時，PostgreSQL 會支援 NOT VALID 選項。這個選項會指示 PostgreSQL 略過子資料表中現有記錄的參照完整性檢查。

下表概略說明 Oracle 和 PostgreSQL 限制類型之間的主要差異：

Oracle 限制類型	PostgreSQL 適用的 Cloud SQL 支援	PostgreSQL 適用的 Cloud SQL 等同項目
`PRIMARY KEY`	是	`PRIMARY KEY`
`FOREIGN KEY`	是	使用與 Oracle 相同的 ANSI SQL 語法。使用 `ON DELETE` 子句處理 `FOREIGN KEY` 父項記錄刪除的情況。PostgreSQL 提供三個選項，用於處理從父資料表刪除資料，且子資料表由 `FOREIGN KEY` 限制條件參照的情況： `ON DELETE CASCADE` `ON DELETE RESTRICT` `ON DELETE NO ACTION` PostgreSQL 不支援 Oracle 的 `ON DELETE SET NULL` 子句。使用 `ON UPDATE` 子句處理 `FOREIGN KEY` 父項記錄更新的情況。 PostgreSQL 提供三種選項來處理 `FOREIGN KEY` 限制更新事件： `ON UPDATE CASCADE` `ON UPDATE RESTRICT` `ON UPDATE NO ACTION` PostgreSQL 不會自動為外鍵限制的參照欄建立索引。
`UNIQUE`	是	預設會建立 `UNIQUE` 索引。
`CHECK`	是	`CHECK`
`NOT NULL`	是	`NOT NULL`
`REF`	否	不支援。
`DEFERRABLE/NOT DEFERRABLE`	是	`DEFERRABLE/NOT DEFERRABLE`
`INITIALLY IMMEDIATE`	是	`INITIALLY IMMEDIATE`
`INITIALLY DEFERRED`	是	`INITIALLY DEFERRED`
`VALIDATE/NO VALIDATE`	否	不支援。
`ENABLE/DISABLE`	否	預設為啟用。使用 `NOT VALID` 選項時，使用 `ALTER TABLE` 陳述式新增外部鍵或檢查限制到資料表，以便略過現有記錄的參照完整性檢查。
對 VIEW 的限制	否	除了 `VIEW WITH CHECK OPTION` 以外，其他都無法支援。
限制中繼資料	Oracle	`DBA_CONSTRAINTS`
	PostgreSQL	`INFORMATION_SCHEMA.TABLE_CONSTRAINTS`

虛擬資料欄和產生的資料欄

Oracle 的虛擬資料欄會根據其他資料欄的計算結果。這些欄會顯示為一般欄，但其值是根據 Oracle 資料庫引擎即時計算而得，並未儲存在資料庫中。虛擬欄可搭配使用限制、索引、資料表分區和外鍵，但無法透過資料操縱語言 (DML) 作業進行操控。

從功能上來說，PostgreSQL 的產生的資料欄與 Oracle 的虛擬資料欄相當。不過，與 Oracle 不同的是，PostgreSQL 會儲存產生的資料欄，且您必須為每個產生的資料欄指定資料類型，也就是說，這些資料欄會占用儲存空間，就像是一般資料欄一樣。

Oracle 中的虛擬欄範例：

SQL> CREATE TABLE PRODUCTS (
        PRODUCT_ID     INT PRIMARY KEY,
        PRODUCT_TYPE   VARCHAR2(100) NOT NULL,
        PRODUCT_PRICE  NUMBER(6,2) NOT NULL,
        PRICE_WITH_TAX AS (ROUND(PRODUCT_PRICE * 1.01, 2))
);

SQL> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE)
     VALUES(1, 'A', 99.99);

SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID PRODUCT_TYPE         PRODUCT_PRICE PRICE_WITH_TAX
---------- -------------------- ------------- --------------
         1 A                            99.99         100.99

PostgreSQL 中的等效範例：

postgres=> CREATE TABLE PRODUCTS (
postgres(>         PRODUCT_ID     INT PRIMARY KEY,
postgres(>         PRODUCT_TYPE   VARCHAR(100) NOT NULL,
postgres(>         PRODUCT_PRICE  NUMERIC(6,2) NOT NULL,
postgres(>         PRICE_WITH_TAX NUMERIC GENERATED ALWAYS AS (ROUND(PRODUCT_PRICE * 1.01, 2)) STORED
postgres(> );

postgres=> INSERT INTO PRODUCTS(PRODUCT_ID, PRODUCT_TYPE, PRODUCT_PRICE) VALUES(1, 'A', 99.99);

postgres=> SELECT * FROM PRODUCTS;

 product_id | product_type | product_price | price_with_tax
------------+--------------+---------------+----------------
          1 | A            |         99.99 |         100.99
(1 row)

資料表索引

Oracle 和 PostgreSQL 提供多種索引演算法和索引類型，可用於各種應用程式。以下是 PostgreSQL 中可用的索引算法清單：

索引演算法	說明
B 樹狀結構	PostgreSQL 的預設索引類型，用於加快相等和範圍查詢的速度支援所有原始資料類型，可用於擷取 `NULL` 值根據預設，系統會以遞增順序排序索引值，但也可以以遞減順序進行設定
雜湊	用於加快等值搜尋速度比 B 樹索引更有效率，但只能處理相等搜尋
GIN	倒置樹狀索引處理含有多個元件值的資料欄 (例如陣列和文字) 時，比 B 樹狀索引更有效率
GiST	並非單一類型的索引，而是用於定義索引的基礎結構，可支援比一般 B 樹索引更多比較運算子在需要最佳化「最近鄰」搜尋時，可用於幾何資料
SP-GiST	與 GiST 類似，SP-GiST 是用於使用者定義索引策略的基礎架構允許各種非平衡資料結構，例如四叉樹不適用於 PostgreSQL 適用的 Cloud SQL
BRIN	區塊範圍索引儲存資料表實體區塊範圍的摘要適用於具有線性排序順序的資料欄在大型資料表上執行範圍查詢時相當實用

下表比較 Oracle 和 PostgreSQL 的索引類型：

Oracle 索引	說明	由 PostgreSQL 支援	PostgreSQL 等價
點陣圖索引	為每個索引鍵儲存位元資料，最適合為 OLAP 工作負載提供快速資料擷取	否	不適用
B 樹狀索引	最常見的索引類型，適合各種工作負載，可在 `ASC\|DESC` 排序中設定。	是	B 樹索引
複合式索引	建立兩個以上的資料欄，以改善資料擷取效能。索引中的資料欄排序會決定存取路徑。	是	多欄索引建立多欄索引時，最多可指定 32 個欄。
以函式為基礎的索引	儲存在資料表資料欄的值上套用的函式輸出內容。	是	運算式索引
不重複索引	這是一個 B 樹索引，可針對每個資料欄的索引值強制執行 `UNIQUE` 限制。	是	不重複索引
應用程式網域索引	適合用於為非關聯式資料建立索引，例如音訊/影片資料、LOB 資料和其他非文字類型。	否	不適用
隱藏索引	Oracle 功能，可讓您管理、維護及測試索引，而不影響最佳化工具的決策。	否	您可以使用其他解決方案，在讀取/寫入複本上建立額外索引，用於測試，而不會影響持續進行的活動。
索引排序的資料表	一種控制資料在資料表和索引層級儲存方式的索引類型。	否	PostgreSQL 不支援索引排序的資料表。`CLUSTER` 陳述式會指示 PostgreSQL 根據指定索引整理資料表儲存空間。這類表格與 Oracle 的索引排序表格具有類似的用途。不過，叢集是一次性的作業，PostgreSQL 不會在後續更新時維護資料表的結構。需要手動定期建立叢集。
區域和全域索引	用於為 Oracle 資料庫中的分區資料表建立索引。每個索引都定義為 `LOCAL` 或 `GLOBAL`。	否	PostgreSQL 分割作業索引的功能與 Oracle 本機索引相同 (也就是說，索引是在分割層級定義，不支援全域層級)。
分區資料表的部分索引 (Oracle 12c)	在資料表分區的子集上建立索引。支援 `LOCAL` 和 `GLOBAL`。	是	PostgreSQL 的分區功能會將子資料表附加至父資料表。您只能在子表的子集上建立索引。
`CREATE/DROP INDEX`	用於建立及刪除索引的指令。	是	PostgreSQL 支援 `CREATE INDEX` 指令。也支援 `ALTER TABLE tableName ADD INDEX indexName columnName`
`ALTER INDEX ... REBUILD`	重建索引，這可能會導致索引表格上鎖。	需要不同的語法	PostgreSQL 支援使用 `REINDEX` 陳述式重新建構索引。在執行這項作業期間，系統會鎖定資料表，只允許寫入資料表。
`ALTER INDEX ... REBUILD ONLINE`	重新建構索引，但不會在資料表上建立專屬鎖定。	需要不同的語法	PostgreSQL 支援使用 `REINDEX TABLE CONCURRENTLY` 陳述式同時重建索引。在這個模式中，PostgreSQL 會嘗試使用最少的鎖定功能來重建索引，但這可能會導致重建作業需要更多時間和資源。
索引壓縮	用於縮減索引實體大小的功能。	否	不適用
將索引分配給資料表空間	建立索引表格空間，可儲存在與表格資料不同的磁碟上，以減少磁碟 I/O 瓶頸。	否	雖然 PostgreSQL 允許在使用者定義的表格空間中建立索引，但您無法在 PostgreSQL 適用的 Cloud SQL 中建立表格空間，且索引必須在預設表格空間中建構。
索引中繼資料 (資料表/檢視畫面)	Oracle	`DBA_INDEXES DBA_PART_INDEXES DBA_IND_COLUMNS`
	PostgreSQL	`pg_catalog.pg_index pg_catalog.pg_attribute pg_catalog.pg_class`

索引轉換考量事項

在多數情況下，Oracle 索引可以直接轉換為 PostgreSQL 的 B 樹索引，因為這類索引是最常用的索引類型。就像在 Oracle 資料庫中一樣，系統會在資料表的 PRIMARY KEY 欄位上自動建立索引。同樣地，系統會在具有 UNIQUE 限制的欄位上自動建立 UNIQUE 索引。此外，次要索引會使用標準 CREATE INDEX 陳述式建立。

以下範例說明如何將含有多個索引欄位的 Oracle 資料表轉換為 PostgreSQL：

SQL> CREATE TABLE ORA_IDX_TO_PG (
        col1 INT PRIMARY KEY,
        col2 VARCHAR2(60),
        col3 DATE,
        col4 CLOB,
        col5 VARCHAR2(20)
      );

-- Single-field index
SQL> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index
SQL> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index
SQL> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(col3);

-- Function-based index
SQL> CREATE INDEX idx_func_col3 ON
        ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB index
SQL> CREATE INDEX idx_col4 ON
       ora_idx_to_pg(col4) INDEXTYPE IS CTXSYS.CONTEXT;

-- Invisible index
SQL> CREATE INDEX idx_col5_inv ON
        ora_idx_to_pg(col5) INVISIBLE;

-- Drop index
SQL> DROP INDEX idx_col5_inv;

postgres=> CREATE TABLE ORA_IDX_TO_PG (
postgres(> col1 INT PRIMARY KEY,
postgres(> col2 VARCHAR(60),
postgres(> col3 DATE,
postgres(> col4 TEXT,
postgres(> col5 VARCHAR(20)
postgres(> );

-- Single index (supported)
postgres=> CREATE INDEX idx_col2 ON ora_idx_to_pg(col2);

-- Composite index (supported)
postgres=> CREATE INDEX idx_cols3_2 ON ora_idx_to_pg(col3 DESC, col2);

-- Unique index (supported)
postgres=> CREATE UNIQUE INDEX idx_col3_uni ON ora_idx_to_pg(COL3);

-- Function-based index (supported)
postgres=> CREATE INDEX idx_func_col3 ON
postgres->         ora_idx_to_pg(EXTRACT(MONTH FROM col3));

-- CLOB (Supported, but requires different syntax. See Full Text Search for details)
postgres=> CREATE INDEX idx_col4 ON ora_idx_to_pg
postgres->         USING GIN (to_tsvector('english', col4));

-- Invisible index (not supported)
-- Optional - create the index as a B-tree index
postgres=> CREATE INDEX idx_col5 ON ora_idx_to_pg(col5);

-- Drop index
postgres=> DROP INDEX idx_col2;

SQL> SELECT ui.table_name,
            ui.index_name,
            ui.index_type,
            ic.column_name
     FROM user_indexes ui JOIN user_ind_columns ic
     ON ui.index_name = ic.index_name
     WHERE ui.table_name = 'ORA_IDX_TO_PG'
     ORDER BY 4;

postgres=> select distinct
postgres->     t.relname as table_name,
postgres->     i.relname as index_name,
postgres-> pg_get_indexdef(ix.indexrelid) index_definition
postgres-> from
postgres->     pg_class t,
postgres->     pg_class i,
postgres->     pg_index ix
postgres-> where
postgres->     t.oid = ix.indrelid
postgres->     and i.oid = ix.indexrelid
postgres->     and t.relname = 'ora_idx_to_pg'
postgres-> order by
postgres->     t.relname,
postgres->     i.relname;

-- OR Use psql \d command:
postgres=> \d ora_idx_to_pg
                  Table "public.ora_idx_to_pg"
 Column |         Type          | Collation | Nullable | Default
--------+-----------------------+-----------+----------+---------
 col1   | integer               |           | not null |
 col2   | character varying(60) |           |          |
 col3   | date                  |           |          |
 col4   | text                  |           |          |
 col5   | character varying(20) |           |          |
Indexes:
    "ora_idx_to_pg_pkey" PRIMARY KEY, btree (col1)
    "idx_col2" btree (col2)
    "idx_col4" gin (to_tsvector('english'::regconfig, col4))
    "idx_col5" btree (col5)
    "idx_cols3_2" btree (col3 DESC, col2)
    "idx_func_col3" btree (date_part('month'::text, col3))

postgres=>

資料表分區

Oracle 和 PostgreSQL 都提供分區功能，可用於分割大型資料表。這項作業是透過將資料表實體分割為較小的部分來完成，每個部分都包含資料列的橫向子集。分區資料表稱為父項資料表，其資料列會實際儲存在分區中。雖然 PostgreSQL 不支援所有 Oracle 分割類型，但確實支援最常見的類型。

下列各節將說明 PostgreSQL 支援的分割類型，並舉例說明如何建立對應於該類型的分割。

RANGE 分區

這類分區會根據落在指定範圍內的欄值，將資料列指派給分區。每個分區都包含資料列，其分區運算式值位於指定範圍內。請注意，範圍不會跨區重疊。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (store_id);

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (MINVALUE) TO (6);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (6) TO (11);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES FROM (11) TO (16);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES FROM (16) TO (21);

清單分區

與 RANGE 分區類似，LIST 分區會根據資料欄值屬於預先定義的值集，將資料列指派給分區。LIST 分區會明確列出每個分區中顯示的鍵值。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY LIST (store_id);

CREATE TABLE employees_pNorth PARTITION OF employees
 FOR VALUES IN (3,5,6);
CREATE TABLE employees_pEast PARTITION OF employees
 FOR VALUES IN (1,2,10);
CREATE TABLE employees_pWest PARTITION OF employees
 FOR VALUES IN (4,12,13);
CREATE TABLE employees_pCnrl PARTITION OF employees
 FOR VALUES IN (7,8,15);

HASH 分割

如果您希望在所有分區之間均勻分配資料，HASH 分區最適合用於這種情況。資料欄值 (或以要進行雜湊運算的資料欄值為基礎的運算式) 和資料列值會指派給與該雜湊值相對應的分區。雜湊值必須明確指派給分區，且所有插入的值都必須對應至單一分區。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY HASH (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE employees_p2 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE employees_p3 PARTITION OF employees
 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

多層級分區

多層分區是一種方法，可為單一資料表建立分區階層。每個分區進一步劃分為多個不同的分區。子分區數量可能因分區而異。

示例

CREATE TABLE sales (
 Saleid    INT,
 sale_date DATE,
 cust_code VARCHAR(15),
 income    DECIMAL(8,2))
PARTITION BY RANGE(date_part('year', sale_date));

CREATE TABLE sales_2019 PARTITION OF sales
 FOR VALUES FROM (2019) TO (2020)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2019_q1 PARTITION OF sales_2019
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2019_q2 PARTITION OF sales_2019
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2019_q3 PARTITION OF sales_2019
 FOR VALUES FROM (7) TO (10);
CREATE TABLE sales_2019_q4 PARTITION OF sales_2019
 FOR VALUES FROM (10) TO (13);

CREATE TABLE sales_2020 PARTITION OF sales
 FOR VALUES FROM (2020) TO (2021)
 PARTITION BY RANGE(date_part('month', sale_date));

CREATE TABLE sales_2020_q1 PARTITION OF sales_2020
 FOR VALUES FROM (1) TO (4);
CREATE TABLE sales_2020_q2 PARTITION OF sales_2020
 FOR VALUES FROM (4) TO (7);
CREATE TABLE sales_2020_h2 PARTITION OF sales_2020
 FOR VALUES FROM (7) TO (13);

附加或卸除分割區

在 PostgreSQL 中，您可以新增或移除父資料表的分區。已分離的區隔日後可重新連結至相同的資料表。此外，重新連結分區時，您可以指定新的分區條件，以便調整分區邊界。

示例

CREATE TABLE employees (
 empid     INT,
 fname     VARCHAR(30),
 lname     VARCHAR(30),
 hired     DATE,
 separated DATE,
 job_code  INT,
 store_id  INT)
 PARTITION BY RANGE (date_part('year', hired));

CREATE TABLE employees_p0 PARTITION OF employees
 FOR VALUES FROM (2010) TO (2015);
CREATE TABLE employees_p1 PARTITION OF employees
 FOR VALUES FROM (2015) TO (2020);

-- changing partition boundaries
BEGIN TRANSACTION;
ALTER TABLE employees DETACH PARTITION employees_p1;
ALTER TABLE employees ATTACH PARTITION employees_p1 FOR VALUES FROM (2015) TO (2022);
COMMIT TRANSACTION;

下表說明 Oracle 和 PostgreSQL 適用的 Cloud SQL 區隔類型相等的情況，以及建議轉換的情況：

Oracle 分割類型	由 PostgreSQL 支援	PostgreSQL 實作
`RANGE` 個分區	是	`PARTITION BY RANGE`
`LIST` 個分區	是	`PARTITION BY LIST`
`HASH` 個分區	是	`PARTITION BY HASH`
`SUB-PARTITIONING`	是	多層次分區
間隔分區	否	不支援
分區建議工具	否	不支援
偏好設定分區	否	不支援
以虛擬資料欄為基礎的分區	否	如要解決這個問題，建議您直接使用虛擬資料欄運算式進行分區： `CREATE TABLE users ( id INT, username VARCHAR(20), first_letter VARCHAR(1) GENERATED ALWAYS AS ( UPPER(SUBSTR(TRIM(username), 1, 1)) ) STORED ) PARTITION BY LIST (UPPER(SUBSTR(TRIM(username), 1, 1)));`
自動清單分割	否	不支援
分割分區	否	如要解決這個問題，請考慮分離或連結資料表分區，以調整分區邊界
交換分區	是	`DETACH / ATTACH PARTITION`
多型別分區 (複合分區)	是	多層次分區
分區中繼資料	Oracle	`DBA_TAB_PARTITIONS DBA_TAB_SUBPARTITIONS`
	PostgreSQL	`pg_catalog.pg_class pg_catalog.pg_partitioned_table`

以下範例是兩個平台建立資料表分區的並排比較。請注意，PostgreSQL 不支援在 CREATE TABLE 指令的 PARTITIONS 子句中參照表格空間。

Oracle 實作

CREATE TABLE employees ( empid NUMBER, fname VARCHAR2(30), lname VARCHAR2(30), hired DATE, separated DATE, job_code NUMBER, store_id NUMBER) PARTITION BY LIST (store_id) ( PARTITION employees_pNorth VALUES (3,5,6) TABLESPACE users, PARTITION employees_pEast VALUES (1,2,10) TABLESPACE users, PARTITION employees_pWest VALUES (4,12,13) TABLESPACE users, PARTITION employees_pCnrl VALUES (7,8,15) TABLESPACE users );

PostgreSQL 實作

CREATE TABLE employees ( empid INT, fname VARCHAR(30), lname VARCHAR(30), hired DATE, separated DATE, job_code INT, store_id INT) PARTITION BY LIST (store_id); CREATE TABLE employees_pNorth PARTITION OF employees FOR VALUES IN (3,5,6); CREATE TABLE employees_pEast PARTITION OF employees FOR VALUES IN (1,2,10); CREATE TABLE employees_pWest PARTITION OF employees FOR VALUES IN (4,12,13); CREATE TABLE employees_pCnrl PARTITION OF employees FOR VALUES IN (7,8,15);

臨時資料表

在 Oracle 資料庫中，臨時資料表稱為 GLOBAL TEMPORARY TABLES，而在 PostgreSQL 中，則簡稱為臨時資料表。兩個平台的臨時資料表基本功能相同。不過，兩者之間有一些明顯差異：

Oracle 會儲存臨時表結構，以便重複使用，即使資料庫重新啟動後也一樣；而 PostgreSQL 則只會在工作階段期間儲存臨時表。
不同使用者只要具備適當的權限，就能存取 Oracle 資料庫中的臨時資料表。相較之下，除非臨時資料表是以結構定義限定名稱參照，否則只能在建立臨時資料表的會話期間存取 PostreSQL 中的臨時資料表。
在 Oracle 資料庫中，GLOBAL 和 LOCAL 臨時表格之間存在差異，可指定表格內容是全域還是會話專屬。基於相容性考量，PostgreSQL 支援 GLOBAL 和 LOCAL 關鍵字，但這些關鍵字不會影響資料的顯示方式。
如果在建立暫時資料表時省略 ON COMMIT 子句，Oracle 資料庫中的預設行為為 ON COMMIT DELETE ROWS，也就是說 Oracle 會在每次提交後截斷暫時資料表。相較之下，PostgreSQL 的預設行為是在每次提交後，保留暫時表格中的資料列。

下表列出 Oracle 和 Cloud SQL for PostgreSQL 之間的臨時表差異。

臨時資料表功能	Oracle 實作	PostgreSQL 實作
語法	`CREATE GLOBAL TEMPORARY TABLE`	`CREATE TEMPORARY TABLE`
無障礙功能	可透過多個工作階段存取	除非使用結構定義限定名稱參照，否則只能透過建立者的工作階段存取
索引支援	是	是
外鍵支援	是	是
保留 DDL	是	否
`ON COMMIT` 預設動作	系統會刪除記錄	保留記錄
`ON COMMIT PRESERVE ROWS`	是	是
`ON COMMIT DELETE ROWS`	是	是
`ON COMMIT DROP`	否	是
`ALTER TABLE` 支援	是	是
收集統計資料	`DBMS_STATS.GATHER_TABLE_STATS`	`ANALYZE`
Oracle 12c `GLOBAL_TEMP_` `TABLE_STATS`	`DBMS_STATS.SET_TABLE_PREFS`	`ANALYZE`

未使用的資料欄

Oracle 的功能會將特定欄標示為 UNUSED，這項功能通常用於從資料表中移除欄，但不會實際移除欄資料。這樣可避免從大型資料表中刪除資料欄時，發生可能的大量負載。

在 PostgreSQL 中，刪除大型資料欄不會從實體儲存空間中移除資料欄資料，因此即使是大型資料表，刪除作業也能快速完成。您不需要將資料欄標示為 UNUSED，就像在 Oracle 資料庫中一樣。系統會透過新的 DML 陳述式或後續 VACUUM 作業，回收已刪除資料欄所占用的空間。

唯讀表格

唯讀資料表是 Oracle 的功能，可使用 ALTER TABLE 指令將資料表標示為唯讀。在 Oracle 12c R2 中，這項功能也適用於含有分區和子分區的表格。PostgreSQL 不提供等同的功能，但有兩種可能的解決方法：

為特定使用者授予資料表的 SELECT 權限。請注意，這不會妨礙資料表擁有者對資料表執行 DML 作業。
建立 PostgreSQL 適用的 Cloud SQL 唯讀備用資源，並將使用者導向唯讀資料表的備用資源。這個解決方案需要在現有的 PostgreSQL 適用 Cloud SQL 執行個體中新增唯讀備用資源。

建立資料庫觸發事件，在 DML 陳述式上引發例外狀況，例如：

-- Define trigger function
CREATE OR REPLACE FUNCTION raise_readonly_exception() RETURNS TRIGGER AS $$
BEGIN
  RAISE EXCEPTION 'Table is readonly!';
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';

-- Fire trigger when DML statements is executed on read only table
CREATE TRIGGER myTable_readonly_trigger
BEFORE INSERT OR UPDATE OR DELETE OR TRUNCATE ON myTable FOR EACH STATEMENT
EXECUTE PROCEDURE raise_readonly_exception();

-- Testing the trigger
postgres=> INSERT INTO myTable (id) VALUES (1);
ERROR:  Table is readonly!
CONTEXT:  PL/pgSQL function raise_readonly_exception() line 3 at RAISE
postgres=>

字元集

Oracle 和 PostgreSQL 都支援多種字元集、排序和萬國碼，包括單位元組和多位元組語言。此外，位於同一執行個體的 PostgreSQL 資料庫可以使用不同的字元集進行設定。請參閱 PostgreSQL 支援的字元集合清單。

在 Oracle 資料庫中，字元集會在資料庫層級 (Oracle 12g R1 或更早版本) 或可插拔資料庫層級 (Oracle 12g R2 或更高版本) 指定。在 PostgreSQL 中，建立新的 PostgreSQL 適用的 Cloud SQL 執行個體時，系統會指定預設的字元集。在該例項中建立的每個資料庫都可以使用不同的字元集建立。每個資料欄都可以指定排序順序和字元分類。

示例

-- Create a database using UTF-8 character set and ja_JP.UTF collation
postgres=> CREATE DATABASE jpdb WITH ENCODING 'UTF8' LC_COLLATE='ja_JP.UTF8' LC_CTYPE='ja_JP.UTF8' TEMPLATE=template0;

-- Query the character set and collation settings of all databases
postgres=> SELECT datname AS DATABASE_NAME, datcollate AS LC_COLLATE, datctype AS LC_CTYPE from pg_database;
 database_name | lc_collate |  lc_ctype
---------------+------------+------------
 cloudsqladmin | en_US.UTF8 | en_US.UTF8
 template0     | en_US.UTF8 | en_US.UTF8
 template1     | en_US.UTF8 | en_US.UTF8
 postgres      | en_US.UTF8 | en_US.UTF8
 jpdb          | ja_JP.UTF8 | ja_JP.UTF8
(5 rows)

-- Alternatively, use psql \l command to query the database settings
postgres=> \l
                                                List of databases
     Name      |       Owner       | Encoding |  Collate   |   Ctype    |            Access privileges
---------------+-------------------+----------+------------+------------+-----------------------------------------
 cloudsqladmin | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 |
 postgres      | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =Tc/cloudsqlsuperuser                  +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser+
               |                   |          |            |            | testuser=CTc/cloudsqlsuperuser
 template0     | cloudsqladmin     | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqladmin                       +
               |                   |          |            |            | cloudsqladmin=CTc/cloudsqladmin
 template1     | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 | =c/cloudsqlsuperuser                   +
               |                   |          |            |            | cloudsqlsuperuser=CTc/cloudsqlsuperuser
-- Specifying column level collation
postgres=> CREATE TABLE test1 (
postgres(>     a text COLLATE "de_DE",
postgres(>     b text COLLATE "es_ES"
postgres(> );

瀏覽次數

PostgreSQL 支援簡單和複雜的檢視畫面。就建立檢視畫面的選項而言，Oracle 和 PostgreSQL 之間有一些差異。下表列出這些差異。

Oracle 檢視功能	說明	PostgreSQL 適用的 Cloud SQL 支援	轉換考量事項
`FORCE`	建立檢視表時，不驗證來源資料表/檢視表是否存在。	否	沒有等同的選項。
`CREATE OR REPLACE`	建立不存在的檢視畫面，或覆寫現有檢視畫面。	是	PostgreSQL 支援用於檢視畫面的 `CREATE OR REPLACE` 指令。
`WITH CHECK OPTION`	指定對檢視畫面執行 DML 作業時的強制執行層級。	是	預設值為 `CASCADED`，會導致系統也評估參照的檢視畫面。 `LOCAL` 關鍵字只會評估目前的檢視畫面。
`WITH READ-ONLY`	僅允許在檢視畫面上執行讀取作業。禁止 DML 作業。	否	解決方法是將資料檢視的 SELECT 權限授予所有使用者。
`VISIBLE \| INVISIBLE` (Oracle 12c)	指定以檢視畫面為依據的資料欄是否對使用者顯示。	否	請只使用必要欄位建立 `VIEW`。

以下轉換範例說明如何將檢視項從 Oracle 轉換為 Cloud SQL PostgreSQL。

-- Create view to retrieve employees from department 100 using the WITH CHECK -- OPTION option
SQL> CREATE OR REPLACE FORCE VIEW vw_emp_dept100
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID=100
WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
SQL> UPDATE vw_emp_dept100
     SET salary=salary+1000;

postgres=> CREATE OR REPLACE VIEW vw_emp_dept100
postgres-> AS
postgres-> SELECT EMPLOYEE_ID,
postgres->        FIRST_NAME,
postgres->        LAST_NAME,
postgres->        SALARY,
postgres->        DEPARTMENT_ID
postgres-> FROM EMPLOYEES
postgres-> WHERE DEPARTMENT_ID=100
postgres-> WITH CHECK OPTION;

-- Perform an UPDATE operation on the VIEW
postgres=> UPDATE vw_emp_dept100
postgres-> SET salary=salary+1000;

-- Update one employee department id to 60
postgres=> UPDATE vw_emp_dept100
postgres-> SET DEPARTMENT_ID=60
postgres-> WHERE EMPLOYEE_ID=110;

ERROR:  new row violates check option for view "vw_emp_dept100"
DETAIL:  Failing row contains (110, John, Chen, JCHEN, 515.124.4269, 1997-09-28, FI_ACCOUNT, 9200.00, null, 108, 60).

查看存取權管理：

檢視表的擁有者必須具備基礎資料表的權限，才能建立檢視表。資料檢視的使用者需要在資料檢視上具備適當的 SELECT 權限。透過檢視畫面執行 DML 作業時，他們也需要檢視畫面上的適當 INSERT、UPDATE、DELETE 權限。無論是哪種情況，使用者都不需要基礎資料表的權限。

後續步驟

進一步瞭解 PostgreSQL 使用者帳戶。
探索 Google Cloud 的參考架構、圖表和最佳做法。歡迎瀏覽我們的雲端架構中心。

將 Oracle® 資料庫使用者和結構定義遷移至 PostgreSQL 適用的 Cloud SQL 透過集合功能整理內容 你可以依據偏好儲存及分類內容。