Oracle EBS 12.2 因為導入 Edition-Based Redefinition (EBR)，因此底層架構如下：
Bash
APPLSYS.FND_USER (Table)
│
▼
APPLSYS.FND_USER# (Editioning View)
│
▼
APPS.FND_USER (Synonym)Oracle 官方文件: Developer’s Guide Release 12.2 Part No. E22961-09 要求 所有 SQL 都應透過 APPS.FND_USER 存取，而不是直接存取 APPLSYS.FND_USER#
使用 SYSDBA 查詢 Synonym
SQL
SELECT owner,
synonym_name,
table_owner,
table_name
FROM dba_synonyms
WHERE owner='APPS'
AND synonym_name='FND_USER';
# 結果
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
----- ------------ ----------- ----------
APPS FND_USER APPLSYS FND_USER#查詢底層 Object
SQL
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE object_name LIKE 'FND_USER%';
# 結果:應該有
PPLSYS FND_USER TABLE
APPLSYS FND_USER# VIEW
APPS FND_USER SYNONYMObject常用SQL查詢
SQL
/*
================================================================================
全面檢查 Object, Synonym, Table, Index, Table-Columns, Statistics
https://dbeaver.com/docs/dbeaver/Client-Side-Scripting/#exasol
================================================================================
*/
--設定參數
@@set p_name = 'FND_USER' @@
-- 確認 Object
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE 1=1
AND object_name = ${p_name}
ORDER BY owner, object_type;
-- 確認 Synonym
SELECT owner,
synonym_name,
table_owner,
table_name
FROM dba_synonyms
WHERE 1=1
AND synonym_name = ${p_name};
--確認實際 Table 定義
SELECT owner,
table_name,
status,
num_rows,
blocks,
last_analyzed
FROM dba_tables
WHERE table_name = ${p_name};
-- 查看 Index
SELECT owner,
index_name,
index_type,
table_name,
status,
num_rows,
distinct_keys,
blevel,
leaf_blocks,
clustering_factor,
last_analyzed
FROM dba_indexes
WHERE 1=1
AND table_name = ${p_name}
ORDER BY owner, index_name;
--確認 Table 裡的 Columns
SELECT owner,
table_name,
column_id,
column_name,
data_type,
data_length,
nullable
FROM dba_tab_columns
WHERE 1=1
AND table_name = ${p_name}
ORDER BY owner, column_id;
--查看是否存在 Invalid Object
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE 1=1
AND object_name = ${p_name}
ORDER BY owner, object_type, object_name;
SELECT owner,
object_name,
object_type,
status
FROM dba_objects
WHERE 1=1
AND object_name IN
(
SELECT index_name
FROM dba_indexes
WHERE table_name = ${p_name}
)
ORDER BY owner, object_type, object_name;
--查看 Statistics
SELECT owner,
table_name,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_size,
last_analyzed
FROM dba_tables
WHERE table_name = ${p_name};
