Example partition range number oracle 8
Aller à la navigation
Aller à la recherche
| Fiche express | |
|---|---|
| Type | Script SQL*Plus d'exemple / tutoriel |
| Rôle | Partitionnement RANGE sur numérique et gestion des partitions |
| Voir aussi | Example transport tablespace |
Example partition range number oracle 8 est un tutoriel complet (utilisable dès Oracle8, seul type de partitionnement disponible à l'époque) illustrant le cycle de vie d'une table partitionnée par intervalle (RANGE) sur une colonne numérique : création des tablespaces dédiés, création de la table et de ses index partitionnés (local et global), puis toutes les opérations de maintenance courantes — déplacement, ajout, split, suppression, troncature et échange de partition — avec, à chaque étape, l'effet sur l'état (USABLE/UNUSABLE) des index concernés.
Ce script est un exemple pédagogique de démonstration ; les noms de tablespaces, chemins de fichiers et données insérées sont fictifs et purement illustratifs.
Script
1. Préparation : tablespaces et table partitionnée
CONNECT scott/tiger
-- Nettoyage préalable (idempotence du script de démo)
DROP TABLE emp_part CASCADE CONSTRAINTS;
DROP VIEW less_view;
DROP TABLE new_less CASCADE CONSTRAINTS;
DROP TABLE less50 CASCADE CONSTRAINTS;
DROP TABLE less100 CASCADE CONSTRAINTS;
DROP TABLE less150 CASCADE CONSTRAINTS;
DROP TABLE less200 CASCADE CONSTRAINTS;
-- Tablespaces de données : un par partition (part_1 à part_10), plus
-- un tablespace "max" (dernière partition MAXVALUE) et un tablespace
-- "move" (utilisé pour la démonstration de déplacement de partition).
-- Motif répété à l'identique pour chaque numéro de 1 à 10 :
CREATE TABLESPACE part_1_data_tbs
LOGGING DATAFILE '/u10/app/oradata/DEMO/part_1_data_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- ... répéter pour part_2_data_tbs jusqu'à part_10_data_tbs ...
CREATE TABLESPACE part_max_data_tbs
LOGGING DATAFILE '/u10/app/oradata/DEMO/part_max_data_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE part_move_data_tbs
LOGGING DATAFILE '/u10/app/oradata/DEMO/part_move_data_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- Même motif pour les tablespaces d'index (part_1_idx_tbs à part_10_idx_tbs,
-- part_max_idx_tbs, part_move_idx_tbs), sur un point de montage séparé :
CREATE TABLESPACE part_1_idx_tbs
LOGGING DATAFILE '/u09/app/oradata/DEMO/part_1_idx_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- ... répéter pour part_2_idx_tbs jusqu'à part_10_idx_tbs ...
CREATE TABLESPACE part_max_idx_tbs
LOGGING DATAFILE '/u09/app/oradata/DEMO/part_max_idx_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
CREATE TABLESPACE part_move_idx_tbs
LOGGING DATAFILE '/u09/app/oradata/DEMO/part_move_idx_tbs01.dbf' SIZE 10M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- Table partitionnée par RANGE sur une colonne numérique (empno)
CREATE TABLE emp_part (
empno NUMBER(15) NOT NULL
, ename VARCHAR2(100)
, sal NUMBER(10,2)
, deptno NUMBER(15)
)
TABLESPACE users
PARTITION BY RANGE (empno) (
PARTITION emp_part_50_part VALUES LESS THAN (50) TABLESPACE part_1_data_tbs,
PARTITION emp_part_100_part VALUES LESS THAN (100) TABLESPACE part_2_data_tbs,
PARTITION emp_part_150_part VALUES LESS THAN (150) TABLESPACE part_3_data_tbs,
PARTITION emp_part_200_part VALUES LESS THAN (200) TABLESPACE part_4_data_tbs,
PARTITION emp_part_MAX_part VALUES LESS THAN (MAXVALUE) TABLESPACE part_max_data_tbs
);
2. Index locaux et globaux
-- Index LOCAL "préfixé" : la clé (empno) est identique à la clé de
-- partitionnement de la table. Oracle le partitionne automatiquement
-- sur les mêmes bornes que emp_part.
CREATE INDEX emp_part_idx1 ON emp_part(empno)
LOCAL (
PARTITION emp_part_50_part TABLESPACE part_1_idx_tbs,
PARTITION emp_part_100_part TABLESPACE part_2_idx_tbs,
PARTITION emp_part_150_part TABLESPACE part_3_idx_tbs,
PARTITION emp_part_200_part TABLESPACE part_4_idx_tbs,
PARTITION emp_part_MAX_part TABLESPACE part_max_idx_tbs
);
-- Index GLOBAL "préfixé" sur une autre colonne (deptno) : ses bornes de
-- partitionnement sont indépendantes de celles de la table. Un index
-- global DOIT obligatoirement se terminer par une partition MAXVALUE.
CREATE INDEX emp_part_idx2 ON emp_part(deptno)
GLOBAL PARTITION BY RANGE (deptno) (
PARTITION emp_part_D10_part VALUES LESS THAN (10) TABLESPACE part_1_idx_tbs,
PARTITION emp_part_D20_part VALUES LESS THAN (20) TABLESPACE part_2_idx_tbs,
PARTITION emp_part_D30_part VALUES LESS THAN (30) TABLESPACE part_3_idx_tbs,
PARTITION emp_part_D40_part VALUES LESS THAN (40) TABLESPACE part_4_idx_tbs,
PARTITION emp_part_D50_part VALUES LESS THAN (50) TABLESPACE part_5_idx_tbs,
PARTITION emp_part_D60_part VALUES LESS THAN (60) TABLESPACE part_6_idx_tbs,
PARTITION emp_part_D70_part VALUES LESS THAN (70) TABLESPACE part_7_idx_tbs,
PARTITION emp_part_D80_part VALUES LESS THAN (80) TABLESPACE part_8_idx_tbs,
PARTITION emp_part_D90_part VALUES LESS THAN (90) TABLESPACE part_9_idx_tbs,
PARTITION emp_part_D100_part VALUES LESS THAN (100) TABLESPACE part_10_idx_tbs,
PARTITION emp_part_DMAX_part VALUES LESS THAN (MAXVALUE) TABLESPACE part_max_idx_tbs
);
-- Jeu de données de démonstration (extrait représentatif ; le script
-- original insère ~150 lignes réparties sur toutes les partitions)
INSERT INTO emp_part VALUES (10, 'DUPONT', 185000.00, 10);
INSERT INTO emp_part VALUES (55, 'MARTIN', 165000.00, 20);
INSERT INTO emp_part VALUES (101, 'DURAND', 112000.00, 30);
INSERT INTO emp_part VALUES (151, 'BERNARD', 44000.00, 40);
INSERT INTO emp_part VALUES (204, 'PETIT', 95000.00, 50);
-- ... etc. jusqu'à couvrir chaque tranche de partition ...
COMMIT;
3. Déplacer une partition et reconstruire les index
-- MOVE PARTITION recolle les données / réduit la fragmentation / change
-- de tablespace. Elle rend UNUSABLE la partition d'index local
-- correspondante, ainsi que TOUTES les partitions de tout index global.
ALTER TABLE emp_part MOVE PARTITION emp_part_50_part TABLESPACE part_move_data_tbs;
-- Reconstruction de l'index LOCAL impacté : soit partition par partition...
ALTER INDEX emp_part_idx1 REBUILD PARTITION emp_part_50_part TABLESPACE part_move_idx_tbs;
-- ... soit en une fois via la table (ne permet pas de changer de tablespace) :
-- ALTER TABLE emp_part MODIFY PARTITION emp_part_50_part REBUILD UNUSABLE LOCAL INDEXES;
-- Reconstruction de l'index GLOBAL : chaque partition doit être reconstruite
-- individuellement (ou, plus simple pour un index global : DROP + CREATE).
ALTER INDEX emp_part_idx2 REBUILD PARTITION emp_part_D10_part;
ALTER INDEX emp_part_idx2 REBUILD PARTITION emp_part_D20_part;
-- ... répéter pour chaque partition D30 à DMAX ...
ALTER INDEX emp_part_idx2 REBUILD PARTITION emp_part_DMAX_part;
4. Ajouter une partition (ADD / SPLIT)
-- ADD PARTITION échoue si la borne haute actuelle est déjà MAXVALUE :
-- ALTER TABLE emp_part ADD PARTITION emp_part_250_part
-- VALUES LESS THAN (250) TABLESPACE part_5_data_tbs;
-- ORA-14074: partition bound must collate higher than that of the last partition
-- SPLIT PARTITION est alors la seule solution : elle découpe la dernière
-- partition (MAXVALUE) en deux, et rend UNUSABLE les partitions locales
-- concernées si elles contiennent des données, ainsi que tout index global.
ALTER TABLE emp_part
SPLIT PARTITION emp_part_MAX_part AT (250)
INTO (
PARTITION emp_part_250_part TABLESPACE part_5_data_tbs
, PARTITION emp_part_MAX_part TABLESPACE part_max_data_tbs
);
ALTER INDEX emp_part_idx1 REBUILD PARTITION emp_part_250_part TABLESPACE part_5_idx_tbs;
ALTER INDEX emp_part_idx1 REBUILD PARTITION emp_part_MAX_part TABLESPACE part_max_idx_tbs;
-- + reconstruction de toutes les partitions de l'index global emp_part_idx2
-- (même séquence que section 3).
5. Supprimer, tronquer et fusionner des partitions
-- DROP PARTITION retire une partition de table (et ses lignes). Toutes
-- les partitions de l'index global deviennent UNUSABLE.
ALTER TABLE emp_part DROP PARTITION emp_part_100_part;
-- + reconstruction de toutes les partitions de l'index global.
-- On ne peut pas DROP directement une partition d'index LOCAL (elle suit
-- automatiquement la table). Pour un index GLOBAL vide, DROP est possible,
-- mais rend UNUSABLE la partition immédiatement supérieure :
ALTER INDEX emp_part_idx2 DROP PARTITION emp_part_D80_part;
ALTER INDEX emp_part_idx2 REBUILD PARTITION emp_part_D90_part;
-- TRUNCATE PARTITION vide une partition en conservant sa structure.
-- Les index locaux restent USABLE (Oracle les maintient à jour), mais
-- toutes les partitions de l'index global deviennent UNUSABLE.
ALTER TABLE emp_part TRUNCATE PARTITION emp_part_200_part;
-- + reconstruction de toutes les partitions de l'index global.
-- SPLIT PARTITION peut aussi être utilisée au milieu de l'intervalle
-- (pas uniquement sur la dernière partition MAXVALUE) :
ALTER TABLE emp_part
SPLIT PARTITION emp_part_150_part AT (100)
INTO (
PARTITION emp_part_100_part TABLESPACE part_2_data_tbs
, PARTITION emp_part_150_part TABLESPACE part_3_data_tbs
);
ALTER INDEX emp_part_idx1 REBUILD PARTITION emp_part_100_part TABLESPACE part_2_idx_tbs;
ALTER INDEX emp_part_idx1 REBUILD PARTITION emp_part_150_part TABLESPACE part_3_idx_tbs;
-- + reconstruction de toutes les partitions de l'index global.
6. EXCHANGE PARTITION : migrer une vue partitionnée V7
-- Cas d'usage historique : convertir une "vue partitionnée" (UNION ALL
-- de plusieurs tables, technique héritée d'Oracle7) en véritable table
-- partitionnée, sans réécrire physiquement les données.
CREATE TABLE less50 (empno NUMBER(15), empname VARCHAR2(100));
CREATE TABLE less100 (empno NUMBER(15), empname VARCHAR2(100));
CREATE TABLE less150 (empno NUMBER(15), empname VARCHAR2(100));
CREATE TABLE less200 (empno NUMBER(15), empname VARCHAR2(100));
CREATE VIEW less_view AS
SELECT * FROM less50 UNION ALL
SELECT * FROM less100 UNION ALL
SELECT * FROM less150 UNION ALL
SELECT * FROM less200;
-- Table partitionnée vide, de structure identique aux tables sources
CREATE TABLE new_less (
empno NUMBER(15)
, empname VARCHAR2(100)
)
PARTITION BY RANGE (empno) (
PARTITION new_less_50_part VALUES LESS THAN (50)
, PARTITION new_less_100_part VALUES LESS THAN (100)
, PARTITION new_less_150_part VALUES LESS THAN (150)
, PARTITION new_less_200_part VALUES LESS THAN (200)
);
-- Quelques lignes de test par table source (données fictives)
INSERT INTO less50 VALUES (10, 'DUPONT');
INSERT INTO less100 VALUES (60, 'MARTIN');
INSERT INTO less150 VALUES (110, 'DURAND');
INSERT INTO less200 VALUES (160, 'BERNARD');
COMMIT;
-- Bascule de chaque table dans la partition correspondante : opération
-- quasi instantanée, purement dictionnaire, sans déplacement physique
-- de données. Les structures (types, colonnes, tailles) doivent être
-- strictement identiques entre table source et table partitionnée.
ALTER TABLE new_less EXCHANGE PARTITION new_less_50_part WITH TABLE less50 WITH VALIDATION;
ALTER TABLE new_less EXCHANGE PARTITION new_less_100_part WITH TABLE less100 WITH VALIDATION;
ALTER TABLE new_less EXCHANGE PARTITION new_less_150_part WITH TABLE less150 WITH VALIDATION;
ALTER TABLE new_less EXCHANGE PARTITION new_less_200_part WITH TABLE less200 WITH VALIDATION;
7. Effet d'un index UNUSABLE sur les requêtes
-- Un FULL TABLE SCAN reste autorisé : il ne dépend pas de l'index.
SELECT * FROM emp_part;
-- Une requête qui a besoin de l'index partiellement UNUSABLE échoue :
SELECT * FROM emp_part WHERE empno < 200;
-- ORA-01502: index 'SCOTT.EMP_PART_IDX1' or partition of such index is in unusable state
-- Les DML restent possibles tant qu'ils ne touchent pas la partition
-- d'index UNUSABLE...
DELETE FROM emp_part WHERE empno > 100; -- OK si la partition haute n'est pas concernée
-- ... mais échouent avec la même erreur ORA-01502 dès qu'ils requièrent
-- la partition d'index UNUSABLE.
Points clés
- Un index local est automatiquement partitionné sur les mêmes bornes que la table ; une opération de maintenance sur une partition de table (MOVE, SPLIT, DROP, TRUNCATE) ne rend
UNUSABLEque la partition d'index locale correspondante — et uniquement si elle contenait des données. - Un index global n'est pas aligné sur les partitions de la table : la moindre opération de maintenance sur une partition de la table (sauf
TRUNCATEqui préserve les index locaux) rendUNUSABLEl'ensemble de ses partitions, qu'il faut reconstruire une par une (ou recréer entièrement, plus efficace pour un gros index). SPLIT PARTITIONest la seule façon d'étendre l'intervalle au-delà d'une partition déjà bornée parMAXVALUE(unADD PARTITIONdirect échoue avecORA-14074).EXCHANGE PARTITION ... WITH TABLEéchange les métadonnées de segment entre une table et une partition de structure identique, sans déplacement physique des données — utile pour charger en masse (via une table de staging) ou migrer d'anciennes vues partitionnées.
Voir aussi
- Example transport tablespace — autre script d'exemple de manipulation de tablespaces