[10g] Suppression dans une base

Version imprimable

16/12/2008, 09h57
ApprentiOracle

[10g] Suppression dans une base

Bonjour,

J'alimente tout les jours un infocentre en base oracle 10g avec de nouvelles données.

J'ai fait un script de purge afin de supprimer les données qui ne sont plus "valides".

Par contre, mon tablespace grandit sans cesse. Je suis toujours obligé d'augmenter la taille de mon fichier dbf alors que le nombre de données insérés tous les jours reste égal.

Ma purge se fait via des delete. Je sais que le delete ne rend pas la place...

Mes questions :
y'a t'il une commande qui me permettrait de diminuer la taille de mon tablespace ? :P

Ou serais je obligé de faire un export/import ?? :(

Merci à vous.
16/12/2008, 10h02
fatsora

tu as plusieurs methodes plus interessantes

SHRINk

http://download.oracle.com/docs/cd/B...htm#ADMIN10161

alter table move;
suivi de rebuild index

create table as select
suivi index ,contrainte,grant

....
insert /*+append */

...
16/12/2008, 10h34
ApprentiOracle

Citation:

Envoyé par fatsora

tu as plusieurs methodes plus interessantes
SHRINk
http://download.oracle.com/docs/cd/B...htm#ADMIN10161

alter table move;
suivi de rebuild index

create table as select
suivi index ,contrainte,grant

....
insert /*+append */

...

Bonjour fatsora,

Merci déjà pour tes réponses.

je viens de tester le shrink sur une petite table ( > 1,4 Go) et ca fonctionne bien.

Par contre, j'ai des tables qui font plus de 22 Go et la j'ai peur que le shrink ne soit pas la solution idéale. Mais bon je vais tester quand meme.

Pour "l'alter table move", peux t'on le faire sur le meme tablespace, sans perte de données ?
Exemple : si ma table toto est dans le tablespace titi, est ce que je peux faire un : alter table toto move titi ?
16/12/2008, 10h45
fatsora

tu as le choix entre changer de tablespace et rester dans le meme.

Sur de grosses table tu peux faire Create table as select
insert /*+ append */

Changer de tablespace peut avoir des interets : genre mettre sur d'autres disque physiques ....
et resizer facilement apres l'ancien tablespace.

tu ne risques pas de pertes en faisant un move ... sur le meme tablespace
16/12/2008, 11h29
Pomalaix

Citation:

Envoyé par ApprentiOracle

Par contre, mon tablespace grandit sans cesse. Je suis toujours obligé d'augmenter la taille de mon fichier dbf alors que le nombre de données insérés tous les jours reste égal.

Ce n'est en principe pas normal.
Ca laisse à penser que vous chargez systématiquement vos données au-delà du HWM.
Comment les chargez-vous ? Par SQL*Loader en chemin direct ? En INSERT +APPEND ?

ca peut arriver avec un install par defaut

tout a OUI OUI par DBCA

apres voyons comment le tablespace users a été créé par ORACLE

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLESPACE USERS DATAFILE 
  'D:\ORACLE\PRODUCT\ORADATA\TEST\USERS01.DBF' SIZE 1262080K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

apres je reactive scott/tiger

je teste grosse table ,insertion de 10 millions de lignes

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
 
create table big_table
    as
    select rownum id, a.*
      from all_objects a
     where 1=0;
 
 
 declare
        l_cnt number;
        l_rows number := &1;
    begin
        insert 
        into big_table
        select rownum, a.*
          from all_objects a;
 
       l_cnt := sql%rowcount;
 
       commit;
 
       while (l_cnt < l_rows)
       loop
           insert  into big_table
           select rownum+l_cnt,
                  OWNER, OBJECT_NAME, SUBOBJECT_NAME,
                  OBJECT_ID, DATA_OBJECT_ID,
                  OBJECT_TYPE, CREATED, LAST_DDL_TIME,
                  TIMESTAMP, STATUS, TEMPORARY,
                  GENERATED, SECONDARY
             from big_table
            where rownum <= l_rows-l_cnt;
           l_cnt := l_cnt + sql%rowcount;
           commit;
       end loop;
   end;

TABLESPACE, Totalspace(MB),Used Space(MB),Freespace(MB),%Used,% Free
SYSAUX,210,202,8,96.19,3.81,
SYSTEM,440,430,10,97.73,2.27,
UNDOTBS1,650,649,1,99.85,0.15,
USERS,1233,1222,11,99.11,0.89,

Si j'insere 10 millions de lignes a chaque fois
c'st toujrs plein le tablespace USERS

Citation:

Envoyé par Pomalaix

Ce n'est en principe pas normal.
Ca laisse à penser que vous chargez systématiquement vos données au-delà du HWM.
Comment les chargez-vous ? Par SQL*Loader en chemin direct ? En INSERT +APPEND ?

Bonjour Pomalaix,

Nous utilisons un ETL (Data Integrator) pour inserer les données.

Voici le DDL du tablespace. Est ce qu'ici vu la taille du premier fichier il serait pas préférable de passer le tablespace en BIGFILE plutôt que SMALLFILE ?
Code:

1 2 3 4 5 6 7 CREATE SMALLFILE TABLESPACE "TOTOELD" NOLOGGING DATAFILE '/NAB/base01/donnees/totoeld01.dbf' SIZE 32000M REUSE, '/NAB/base01/donnees/totoeld02.dbf' SIZE 3072M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO

16/12/2008, 17h24
fatsora

voici les conditions optimales pour utiliser BIGIFILE
selon la doc officielle

http://download.oracle.com/docs/cd/B...htm#sthref1165

Bigfile Tablespaces

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. The benefits of bigfile tablespaces are the following:

*

A bigfile tablespace with 8K blocks can contain a 32 terabyte datafile. A bigfile tablespace with 32K blocks can contain a 128 terabyte datafile. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files). Therefore, bigfile tablespaces can significantly enhance the storage capacity of an Oracle Database.
*

Bigfile tablespaces can reduce the number of datafiles needed for a database. An additional benefit is that the DB_FILES initialization parameter and MAXDATAFILES parameter of the CREATE DATABASE and CREATE CONTROLFILE statements can be adjusted to reduce the amount of SGA space required for datafile information and the size of the control file.
*

Bigfile tablespaces simplify database management by providing datafile transparency. SQL syntax for the ALTER TABLESPACE statement lets you perform operations on tablespaces, rather than the underlying individual datafiles.

Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment space management, with three exceptions: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace.

Notes:

*

Bigfile tablespaces are intended to be used with Automatic Storage Management (ASM) or other logical volume managers that supports striping or RAID, and dynamically extensible logical volumes.
*

Avoid creating bigfile tablespaces on a system that does not support striping because of negative implications for parallel query execution and RMAN backup parallelization.
*

Using bigfile tablespaces on platforms that do not support large file sizes is not recommended and can limit tablespace capacity. Refer to your operating system specific documentation for information about maximum supported file sizes.

Je ne pense pas que c'est ton cas ici
16/12/2008, 17h43
Pomalaix

Citation:

Envoyé par fatsora

ca peut arriver avec un install par defaut

tout a OUI OUI par DBCA

Euh, je ne comprends pas ce que votre code est censé démontrer, puisque vous ne faites jamais de suppression.
16/12/2008, 17h47
fatsora

Citation:

Envoyé par Pomalaix

Euh, je ne comprends pas ce que votre code est censé démontrer, puisque vous ne faites jamais de suppression.

vous avez ecris ceci

Citation:

Ca laisse à penser que vous chargez systématiquement vos données au-delà du HWM.
Comment les chargez-vous ? Par SQL*Loader en chemin direct ? En INSERT +APPEND ?

c'est simplement pour dire que avec un install tout par defaut ,et sans faire insert append , on a un tablespace rempli

on remplit un tablespace en inserant jusqu'a preuve du contraire
chez vous non ?
16/12/2008, 21h06
Pomalaix

Citation:

Envoyé par fatsora

c'est simplement pour dire que avec un install tout par defaut ,et sans faire insert append , on a un tablespace rempli

on remplit un tablespace en inserant jusqu'a preuve du contraire
chez vous non ?

Vu comme ça, je peux aussi dire que l'eau ça mouille, mais ça ne fait guère progresser la science...

Ce que notre ami décrit dans sa question, c'est que malgré les suppressions qu'il fait, l'espace n'est jamais réutilisé, et que le tablespace grandit constamment alors que le nombre de lignes n'augmente pas.
17/12/2008, 13h53
lallio

Bonjour,

-- Juste pour étendre les possibilités. Vous parlez de table de 22 GO. Sur quel critère supprimez vous les lignes (date ?). Si vous disposez d'une version enterprise avec option de partitionnement, il serait peut être judicieux de mettre en place le partionnement. Du coup, pour purger il suffirait de supprimer / truncater une partition.

Vu que le move engendre la reconstruction des indexes et un besoin en espace. Une solution peut également etre export / suppression table / import

Après tout dépends des contraintes d'arret de service.

Laurent.
17/12/2008, 14h07
ApprentiOracle
Citation:

Envoyé par lallio

Bonjour,

-- Juste pour étendre les possibilités. Vous parlez de table de 22 GO. Sur quel critère supprimez vous les lignes (date ?). Si vous disposez d'une version enterprise avec option de partitionnement, il serait peut être judicieux de mettre en place le partionnement. Du coup, pour purger il suffirait de supprimer / truncater une partition.
Vu que le move engendre la reconstruction des indexes et un besoin en espace. Une solution peut également etre export / suppression table / import
Après tout dépends des contraintes d'arret de service.
Laurent.

Bonjour Lallio,
oui, le critère de suppression des lignes se fait par une colonne de type date.
Malheureusement nous avons pas la version avec partitionnement.

J'ai testé le shrink sur une table de 9 Go ça a marché. Par contre sur une table de même grosseur, mais qui bouge plus vite (plus d'insertion et plus de suppression), j'obtiens l'erreur suivante (pour info, mon tablespace d'undo fait 3 Go).
Code:

1 2 3 ERREUR a la ligne 1 : ORA-30036: impossible d'etendre le segment par 8 dans le tablespace d'annulation 'UNDO2'
18/12/2008, 10h23
ApprentiOracle

Bonjour à tous.

Ce matin, j'ai lancé un shrink sur une table qui était énorme, et dont le contenu bouge énormément.

Celui a fonctionné sans erreur. Je suis passé d'un tablespace de 31 Go de données (pas la taille maximum!) à 25 Go. Résultat un gain énorme !

Je vais retenter de refaire le shrink sur la table sur laquelle j'avais eu une erreur ORA-30036.

Si tout fonctionne, je retesterai demain pour voir le temps du shrink après l'insertion de données et surpression de données d'un jour pour estimer le temps pris.
Si ca me convient j'insérerais le shrink dans le script de purge.

Cordialement.
18/12/2008, 12h31
Pomalaix

Je reformule votre problème tel que je l'ai compris :

Quand je fais du vélo, j'ai les genoux qui traînent par terre et je m'écorche tout le temps. Après, quand j'enlève mes pansements, ça me tire les poils. Quelle marque de pansements pourriez-vous me recommander pour éviter cet inconvénient ?

Les pseudo-réponses :
prends des pansements pilofacil, ils sont utilisés par la NASA
mets des genouillères
épile-toi les genoux

Et la réponse de bon sens : prends un vélo à ta taille, tes genoux ne traîneront plus par terre !!!

Plutôt que de chercher une méthode pour traiter un problème répétitif intégré comme étant normal, ne vaut-il pas mieux se débrouiller pour que ce problème ne se produise plus ?
Comme je le disais plus haut, le problème, chez vous, c'est manifestement que vos données sont insérées au-dessus du HWM, et c'est donc sur votre procédure d'insertion qu'il faut travailler.
18/12/2008, 15h47
ApprentiOracle

Citation:

Envoyé par Pomalaix

Comme je le disais plus haut, le problème, chez vous, c'est manifestement que vos données sont insérées au-dessus du HWM, et c'est donc sur votre procédure d'insertion qu'il faut travailler.

On est d'accord. Par contre j'utilise un flux etl, (Data integrator) pour insérer les données. Le flux est le plus simple possible et je ne vois pas comment l'améliorer.

je vais quand meme essayer d'y jetter un oeil.

Merci à tous pour votre aide.