[11gR2] Déplacer une colonne CLOB : comment identifier son tablespace?

Déplacer une colonne CLOB : comment identifier son tablespace?

Bonjour,

Comme vous avez dû le constater je pose de multiples questions ces dernières semaines; j'espère que je ne vous saoule pas avec mes problèmes :calim2:

Voici ma nouvelle question : j'ai créé une table avec une colonne CLOB puis j'ai fais un move de celle-ci dans un autre tablespace que celui de sa table. Ce que je voudrais c'est vérifier dans les vues Oracle que cette colonne est bien dans ce tablespace mais je n'arrive pas à faire le lien entre les vues des colonnes et celles des tablespaces car il faut passer par les segments et là il y a quelques chose que je ne comprends pas.

Voici ce que j'ai fais.

1) Création d’une table avec une colonne CLOB dans le même tablespace que les colonnes non LOB.

Code:

CREATE TABLE TEST_DD_CLOB ( TEST_DD_ID NUMBER NOT NULL, TEST_DD_NAME CLOB, CONSTRAINT PK_TEST PRIMARY KEY(TEST_DD_ID));

2) Vérification de la creation de la table
Code:

1 2 3 4 5 6 7 8 9 SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'TEST_DD_CLOB'; OWNER: ACCD_FRA TABLE_NAME: TEST_DD_CLOB TABLESPACE_NAME: DATA STATUS: VALID PCT_FREE: 10 INI_TRANS: 1 MAX_TRANS: 255
3) Vérification de la création automatique par Oracle de l’index sur le CLOB
Oracle génère un nom système pour cet index ; il est de type LOB sur le tablespace par défaut.
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT * FROM DBA_INDEXES WHERE TABLE_NAME = 'TEST_DD_CLOB'; OWNER: ACCD_FRA INDEX_NAME: SYS_IL0000131892C00002$$ INDEX_TYPE: LOB TABLE_OWNER: ACCD_FRA TABLE_NAME: TEST_DD_CLOB TABLE_TYPE: TABLE UNIQUENESS: UNIQUE TABLESPACE_NAME: DATA STATUS: VALID
4) Vérification de la creation des segments de la table et de l’index
Impossible de voir la table ou l’index dans la table dba_segments. En effet on est en mode « Deffered segment creation » et le premier segment de la table ou de l’index sera créé lorsque le premier enregistrement sera inséré.
Code:

1 2 3 4 5 6 7 8 SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TEST_DD_CLOB' ORDER BY 2; No rows selected SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = ' SYS_IL0000131885C00002$$' ORDER BY 2; No rows selected SHOW PARAMETER DEFERRED_SEGMENT_CREATION deferred_segment_creation boolean TRUE
5) Déplacement de la colonne CLOB vers un autre tablespace
Il faut faire un alter table et spécifier le nouveau tablespace.

Code:

ALTER TABLE TEST_DD_CLOB MOVE LOB (TEST_DD_NAME) STORE AS (TABLESPACE USER_DATA);

6) Vérification du déplacement de la colonne CLOB et de l’index
Index : le tablespace de l’index du CLOB a bien été changé de DATA vers USER_DATA et l’index sur la PK est encore sur le tablespace DATA.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 SELECT * FROM DBA_INDEXES WHERE TABLE_NAME = 'TEST_DD_CLOB'; • Index CLOB OWNER: ACCD_FRA INDEX_NAME: SYS_IL0000131892C00002$$ INDEX_TYPE: LOB TABLE_OWNER: ACCD_FRA TABLE_NAME: TEST_DD_CLOB TABLE_TYPE: TABLE UNIQUENESS: UNIQUE TABLESPACE_NAME: USER_DATA STATUS: VALID • Index PK non CLOB OWNER: ACCD_FRA INDEX_NAME: PK_TEST INDEX_TYPE: NORMAL TABLE_OWNER: ACCD_FRA TABLE_NAME: TEST_DD_CLOB TABLE_TYPE: TABLE UNIQUENESS: UNIQUE TABLESPACE_NAME: DATA STATUS: VALID
Vérification que la colonne déplacée a bien changé de tablespace
C'est là que je coince et que j'ai besoin de votre aide :aie:

7) Création d’un premier enregistrement et vérification de la création du segment de la table
Code:

1 2 INSERT INTO TEST_DD_CLOB VALUES (1, ‘TEST DD CLOB’); one row created
La création du segment de la table est OK.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'TEST_DD_CLOB' ORDER BY 2; OWNER: ACCD_FRA SEGMENT_NAME: TEST_DD_CLOB SEGMENT_TYPE: TABLE TABLESPACE_NAME: DATA BYTES: 65536 BLOCKS: 8 EXTENTS: 1 INITIAL_EXTENT: 65536 NEXT_EXTENT: 1048576 MIN_EXTENTS: 1 MAX_EXTENTS: 2147483645 MAX_SIZE: 2147483645
La création du segment de l’index est OK.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'SYS_IL0000131892C00002$$' ORDER BY 2; OWNER: ACCD_FRA SEGMENT_NAME: SYS_IL0000131892C00002$$ SEGMENT_TYPE: LOBINDEX TABLESPACE_NAME: USER_DATA BYTES: 65536 BLOCKS: 8 EXTENTS: 1 INITIAL_EXTENT: 65536 NEXT_EXTENT: 1048576 MIN_EXTENTS: 1 MAX_EXTENTS: 2147483645 MAX_SIZE: 2147483645

C'est sans doute la vue DBA_LOBS qui vous manque, et qui fait le lien entre les tables et les segments LOB.

Code:

1
2
3
4
5
6
7
8
SELECT    l.owner as PROPRIO, 
    l.table_name as NOM_TABLE, 
    segment_name as SEGMENT, 
    s.segment_type as TYPE, 
    s.tablespace_name as TABLESPACE
FROM dba_segments s INNER JOIN dba_lobs l USING (segment_name)
WHERE l.owner='&PROPRIO'
AND l.table_name='&NOM_TABLE';

Un gros merci pour cette réponse rapide :P
Je n'avais pas pensé à regarder les tables dédiées aux lob!

Pour info, je n'ai même pas à regarder dans DBA_SEGMENTS car dans dba_lobs j'ai ce que je voulais : le nom de la colonne et le nom du tablespace associé!
Code:

1 2 3 4 5 6 7 select * from dba_lobs where column_name = 'TEST_DD_NAME'; OWNER: ACCD_FRA TABLE_NAME: TEST_DD_CLOB COLUMN_NAME: TEST_DD_NAME SEGMENT_NAME: SYS_LOB0000131892C00002$$ TABLESPACE_NAME: USER_DATA
Demain je pose une autre question :ptdr:

Déplacer une colonne CLOB : comment identifier son tablespace?

A propos de tes deux indexes un a été déplacé (celui de type LOB) vers le nouveau tablespace,
et l'autre est résté dans l'ancien, ce que tu peux faire c'est tu fais
alter index xxxxxx_pk rebuild nouveau_tablespace ;