L’échange de partitions est couramment mis en œuvre dans les environnements décisionnels, pour accélérer les chargements (Partition Exchange Loading) ou pour effectuer des sauvegardes avec la fonctionnalité des tablespaces transportables.
Pour automatiser l’échange de partitions (avec une table), une instruction « Create Table As Select » (CTAS) peut être utilisée pour créer une table à l’image de la table partitionnée.
Dans le cas de la présence de colonnes de type UNUSED, cette opération génère une erreur ORA-14096. Cet article propose une solution pour résoudre ce problème.
Première étape, créer une table partitionnée T1 :
CREATE TABLE t1 ( id NUMBER NOT NULL, code1 VARCHAR2(60) NOT NULL, code2 VARCHAR2(60) NOT NULL, datetime DATE NOT NULL, timestamp TIMESTAMP(3), comments VARCHAR2(4000), val1 NUMBER(11,2), val2 NUMBER(14), pct NUMBER(5,2), other CLOB ) TABLESPACE users PARTITION BY RANGE (id) ( PARTITION p00001 VALUES LESS THAN (10000), PARTITION p10000 VALUES LESS THAN (20000), PARTITION p20000 VALUES LESS THAN (30000), PARTITION p30000 VALUES LESS THAN (40000), PARTITION p40000 VALUES LESS THAN (50000), PARTITION p50000 VALUES LESS THAN (60000), PARTITION pmore VALUES LESS THAN (MAXVALUE) ); INSERT INTO t1 (id, code1, code2, datetime, timestamp, comments, val1, val2, pct, other) SELECT rownum, owner, object_name, created, last_ddl_time, status, object_id, data_object_id+1, 100, timestamp FROM all_objects; COMMIT;
Rajouter des statistiques étendues, des colonnes avec ou sans valeur par défaut et une colonne virtuelle:
DECLARE
extended_column VARCHAR2(60);
BEGIN
extended_column := DBMS_STATS.CREATE_EXTENDED_STATS (
ownname => USER,
tabname => 'T1',
extension => '(code1, code2)');
DBMS_OUTPUT.PUT_LINE ('Extended column name : ' || extended_column);
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'T1' );
END;
/
ALTER TABLE t1 ADD code3 CHAR(1);
ALTER TABLE t1 ADD code4 CHAR(1) DEFAULT 'X';
ALTER TABLE t1 MODIFY code4 CHAR(1) NOT NULL;
ALTER TABLE t1 ADD indice NUMBER DEFAULT 0;
ALTER TABLE t1 MODIFY indice NOT NULL;
ALTER TABLE t1 ADD val3 GENERATED ALWAYS AS (val1 + val2) VIRTUAL;
ALTER TABLE t1 SET UNUSED COLUMN code3;
Note : L’ajout d’une colonne avec une valeur par défaut est effectué par 2 instructions ALTER pour résoudre l’ORA-600 [kkpoffoc] (Adding a Column To a Table [ID 1377836.1]).
La colonne VAL3 est supprimée logiquement (impératif dans le cas d’une table compressée en mode BASIC par exemple).
CREATE TABLE T2 AS SELECT * FROM T1 WHERE 1=0; ALTER TABLE t1 EXCHANGE PARTITION p10000 WITH TABLE t2 EXCLUDING INDEXES WITHOUT VALIDATION * ERROR at line 1: ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns
Le nombre de colonnes doit être identique, il faudrait donc ajouter la colonne CODE3 et la supprimer logiquement :
ALTER TABLE t2 ADD code3 CHAR(1); ALTER TABLE t2 SET UNUSED COLUMN code3; ALTER TABLE t1 EXCHANGE PARTITION p10000 WITH TABLE t2 EXCLUDING INDEXES WITHOUT VALIDATION * ERROR at line 1: ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns
Même erreur – l’ordre des colonnes est également pris en compte :
SELECT table_name, column_id, nullable, data_default
FROM user_tab_columns
WHERE table_name IN ('T1', 'T2')
ORDER BY table_name, column_id;
Table Column ? Data
Name id Nullable Default
---------- ------- -------- ----------------------------------------
T1 1 N
2 N
3 N
4 N
5 Y
6 Y
7 Y
8 Y
9 Y
10 Y
11 N 'X'
12 N 0
13 Y "VAL1"+"VAL2"
T2 1 N
2 N
3 N
4 N
5 Y
6 Y
7 Y
8 Y
9 Y
10 Y
11 N
12 N
13 Y
SELECT table_name, column_id, internal_column_id, segment_column_id, column_name, hidden_column, virtual_column, nullable, data_default
FROM user_tab_cols
WHERE table_name IN ('T1', 'T2')
ORDER BY table_name, NVL(internal_column_id, segment_column_id);
Internal Segment ? ?
Table Column Column Column Column Hidden Virtual ? Data
Name id id id Name Column Name Nullable Default
---------- ------- -------- ------- ------------------------------ -------- -------- -------- ----------------------------------------
T1 1 1 1 ID NO NO N
2 2 2 CODE1 NO NO N
3 3 3 CODE2 NO NO N
4 4 4 DATETIME NO NO N
5 5 5 TIMESTAMP NO NO Y
6 6 6 COMMENTS NO NO Y
7 7 7 VAL1 NO NO Y
8 8 8 VAL2 NO NO Y
9 9 9 PCT NO NO Y
10 10 10 OTHER NO NO Y
11 SYS_STU#29QF8Y9BUDOW2HCDL47N44 YES YES Y SYS_OP_COMBINED_HASH("CODE1","CODE2")
12 11 SYS_C00012_13030614:27:57$ YES NO Y
11 13 12 CODE4 NO NO N 'X'
12 14 13 INDICE NO NO N 0
13 15 VAL3 NO YES Y "VAL1"+"VAL2"
T2 1 1 1 ID NO NO N
2 2 2 CODE1 NO NO N
3 3 3 CODE2 NO NO N
4 4 4 DATETIME NO NO N
5 5 5 TIMESTAMP NO NO Y
6 6 6 COMMENTS NO NO Y
7 7 7 VAL1 NO NO Y
8 8 8 VAL2 NO NO Y
9 9 9 PCT NO NO Y
10 10 10 OTHER NO NO Y
11 11 11 CODE4 NO NO N
12 12 12 INDICE NO NO N
13 13 13 VAL3 NO NO Y
14 14 SYS_C00014_13030615:14:17$ YES NO Y
La vue USER_TAB_COLS (vs USER_TAB_COLUMNS) fournit des informations complémentaires sur les colonnes cachées et virtuelles.
On peut noter que l’instruction CTAS ne reprend pas les valeurs par défaut et transforme la colonne virtuelle en colonne « normale ».
Il est donc nécessaire de créer une table à l’identique de la table partitionnée, ci-dessous un exemple de procédure pour effectuer cette opération :
CREATE OR REPLACE PACKAGE pck_prepare_exchange
AS
FUNCTION sf_Data_default (
sp_TableName IN VARCHAR2,
sp_ColumnName IN VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES (sf_Data_default, wnds, wnps);
PROCEDURE f_Create_Interim_Table (
sp_TableName IN VARCHAR2,
sp_InterimTableName IN VARCHAR2,
sp_TablespaceName IN VARCHAR2 DEFAULT 'USERS' );
END pck_prepare_exchange;
/
CREATE OR REPLACE PACKAGE BODY pck_prepare_exchange
AS
FUNCTION sf_Data_default (
sp_TableName IN VARCHAR2,
sp_ColumnName IN VARCHAR2) RETURN VARCHAR2
IS
l_LongColumn user_tab_cols.data_default%TYPE;
s_VirtualColumn user_tab_cols.virtual_column%TYPE;
s_HiddenColumn user_tab_cols.hidden_column%TYPE;
BEGIN
SELECT data_default, virtual_column, hidden_column
INTO l_LongColumn, s_VirtualColumn, s_HiddenColumn
FROM user_tab_cols
WHERE table_name = sp_TableName
AND column_name = sp_ColumnName;
IF (l_LongColumn IS NULL) THEN
RETURN NULL;
ELSIF (s_VirtualColumn = 'YES') THEN
RETURN ' GENERATED ALWAYS AS (' || l_LongColumn || ') VIRTUAL VISIBLE ';
ELSIF (l_LongColumn LIKE '%SYS_OP_COMBINED_HASH%') THEN
RETURN ' AS (' || l_LongColumn || ')';
ELSE
RETURN ' DEFAULT ' || l_LongColumn;
END IF;
END sf_Data_default;
PROCEDURE f_Create_Interim_Table (
sp_TableName IN VARCHAR2,
sp_InterimTableName IN VARCHAR2,
sp_TablespaceName IN VARCHAR2 DEFAULT 'USERS' )
IS
s_Template VARCHAR2(32000);
s_Command VARCHAR2(32000);
s_ColumnList VARCHAR2(32000);
n_NbUnused PLS_INTEGER;
BEGIN
s_Template := 'CREATE TABLE <TABLE_NAME> (<COLUMN_LIST>) TABLESPACE <TABLESPACE>';
SELECT LISTAGG('"' || column_name || '" ' ||
data_type ||
DECODE(SUBSTR(data_type, 1, 9),
'TIMESTAMP', NULL,
'INTERVAL ', NULL,
DECODE(data_type,
'NUMBER', DECODE(data_precision || data_scale,
NULL, NULL, '(' || DECODE(data_precision, NULL, '*', data_precision ) || DECODE(data_scale, NULL, NULL, ',' || data_scale ) || ')'
),
'FLOAT', '(' || data_precision || ')',
'LONG', NULL,
'LONG RAW', NULL,
'BLOB', NULL,
'CLOB', NULL,
'NCLOB', NULL,
'BFILE', NULL,
'CFILE', NULL,
'BINARY_FLOAT', NULL,
'BINARY_DOUBLE', NULL,
'MLSLABEL', NULL,
'ROWID', NULL,
'UROWID', NULL,
'DATE', NULL,
'(' ||
NVL(NVL(DECODE(char_length, 0, TO_NUMBER(NULL), char_length), char_col_decl_length), data_length) ||
DECODE(character_set_name, 'CHAR_CS', DECODE(char_used, 'C', ' CHAR', 'B', ' BYTE')) ||
')'
)
) ||
sf_Data_default(table_name, column_name) ||
DECODE(NULLABLE, 'N', ' NOT NULL', NULL),
', ') WITHIN GROUP (ORDER BY internal_column_id)
INTO s_ColumnList
FROM all_tab_cols
WHERE owner = SYS_CONTEXT('USERENV', 'CURRENT_USER')
AND NOT ( virtual_column = 'YES'
AND hidden_column = 'YES' )
AND table_name = sp_TableName;
-- Remplacement des parametres
s_Command := REPLACE(s_Template, '<TABLE_NAME>', sp_InterimTableName);
s_Command := REPLACE(s_Command, '<TABLESPACE>', sp_TablespaceName );
s_Command := REPLACE(s_Command, '<COLUMN_LIST>', s_ColumnList );
-- Execution de la commande
EXECUTE IMMEDIATE s_Command;
DBMS_OUTPUT.PUT_LINE('Executed : '|| s_Command);
-- Passage des colonnes en mode UNUSED
s_Template := 'ALTER TABLE <TABLE_NAME> SET UNUSED COLUMN "<COLUMN_NAME>"';
FOR rec IN ( SELECT column_name
FROM all_tab_cols
WHERE owner = SYS_CONTEXT('USERENV', 'CURRENT_USER')
AND table_name = sp_TableName
AND segment_column_id IS NOT NULL
AND hidden_column = 'YES'
)
LOOP
-- Remplacement des parametres
s_Command := REPLACE(s_Template, '<TABLE_NAME>', sp_InterimTableName);
s_Command := REPLACE(s_Command, '<COLUMN_NAME>', rec.column_name);
-- Execution de la commande
EXECUTE IMMEDIATE s_Command;
DBMS_OUTPUT.PUT_LINE('Executed : '|| s_Command);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error executing : '|| s_Command);
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END f_Create_Interim_Table;
END pck_prepare_exchange;
/
Créer la table T3 avec la procédure f_Create_Interim_Table :
BEGIN
pck_prepare_exchange.f_Create_Interim_Table(
sp_TableName => 'T1',
sp_InterimTableName => 'T3' );
END;
/
Executed : CREATE TABLE T3 ("ID" NUMBER NOT NULL, "CODE1" VARCHAR2(60 BYTE) NOT NULL, "CODE2" VARCHAR2(60 BYTE) NOT NULL, "DATETIME" DATE NOT NULL, "TIMESTAMP"
TIMESTAMP(3), "COMMENTS" VARCHAR2(4000 BYTE), "VAL1" NUMBER(11,2), "VAL2" NUMBER(14,0), "PCT" NUMBER(5,2), "OTHER" CLOB, "SYS_C00012_13030614:27:57$" CHAR(1
BYTE), "CODE4" CHAR(1 BYTE) DEFAULT 'X' NOT NULL, "INDICE" NUMBER DEFAULT 0 NOT NULL, "VAL3" NUMBER GENERATED ALWAYS AS ("VAL1"+"VAL2") VIRTUAL VISIBLE )
TABLESPACE USERS
Executed : ALTER TABLE T3 SET UNUSED COLUMN "SYS_C00012_13030614:27:57$"
Vérifier la création des colonnes :
SELECT table_name, column_id, nullable, data_default
FROM user_tab_columns
WHERE table_name IN ('T3')
ORDER BY table_name, column_id;
Table Column ? Data
Name id Nullable Default
---------- ------- -------- ----------------------------------------
T3 1 N
2 N
3 N
4 N
5 Y
6 Y
7 Y
8 Y
9 Y
10 Y
11 N 'X'
12 N 0
13 Y "VAL1"+"VAL2"
SELECT table_name, column_id, internal_column_id, segment_column_id, column_name, hidden_column, virtual_column, nullable, data_default
FROM user_tab_cols
WHERE table_name IN ('T3')
ORDER BY table_name, NVL(internal_column_id, segment_column_id);
Internal Segment ? ?
Table Column Column Column Column Hidden Virtual ? Data
Name id id id Name Column Name Nullable Default
---------- ------- -------- ------- ------------------------------ -------- -------- -------- -----------------
T3 1 1 1 ID NO NO N
2 2 2 CODE1 NO NO N
3 3 3 CODE2 NO NO N
4 4 4 DATETIME NO NO N
5 5 5 TIMESTAMP NO NO Y
6 6 6 COMMENTS NO NO Y
7 7 7 VAL1 NO NO Y
8 8 8 VAL2 NO NO Y
9 9 9 PCT NO NO Y
10 10 10 OTHER NO NO Y
11 11 SYS_C00011_13030614:27:57$ YES NO Y
11 12 12 CODE4 NO NO N 'X'
12 13 13 INDICE NO NO N 0
13 14 VAL3 NO YES Y "VAL1"+"VAL2"
Les valeurs par défaut sont définies, la colonne virtuelle créée, la colonne UNUSED correctement positionnée (entre la colonne OTHER et CODE4).
Il ne reste plus qu’à tester l’échange de partition avec la table T3 :
ALTER TABLE t1 EXCHANGE PARTITION p20000 WITH TABLE t3 EXCLUDING INDEXES WITHOUT VALIDATION; Table altered. SELECT COUNT(*) FROM T1 PARTITION (p20000); COUNT(*) ---------- 0 SELECT COUNT(*) FROM T3; COUNT(*) ---------- 10000
L’échange de partitions fonctionne correctement.
