Re: Two tables are quite the same, but size of theirs ORA datafiles are different!

On Oct 27, 10:41 am, Big George <jbet..._at_gmail.com> wrote:
> I've got two tables in Oracle 10.1g, Windows 2003.
>
> TableA
> 16,594,824 records
>
> CREATE TABLE TableA
> (
>   NUMDLE  NCHAR(8)                              NOT NULL,
>   DIGVER  NCHAR(1)                              NOT NULL,
>   NUMLIB  NCHAR(6)                              NOT NULL,
>   CODDEP  NCHAR(2)                              NOT NULL,
>   CODPRO  NCHAR(2)                              NOT NULL,
>   CODDIS  NCHAR(2)                              NOT NULL,
>   APEPAT  VARCHAR2(40 BYTE),
>   APEMAT  VARCHAR2(40 BYTE),
>   NOMBRE  VARCHAR2(35 BYTE),
>   FECNAC  NCHAR(8),
>   CODSEX  NCHAR(1),
>   CODGRI  NCHAR(2),
>   CODSTR  NCHAR(1),
>   TIPDOC  NCHAR(1),
>   CONSTRAINT NUMDLETABLEA
>  PRIMARY KEY
>  (NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceA
> PCTFREE    10
> INITRANS   2
> MAXTRANS   255
> STORAGE    (
>             INITIAL          64K
>             MINEXTENTS       1
>             MAXEXTENTS       2147483645
>             PCTINCREASE      0
>             BUFFER_POOL      DEFAULT
>            )
> NOPARALLEL
> MONITORING;
>
> TableB
> 16,494,906 records
>
> CREATE TABLE TableB
> (
>   NUMDLE  NCHAR(8)                              NOT NULL,
>   DIGVER  NCHAR(1)                              NOT NULL,
>   NUMLIB  NCHAR(6)                              NOT NULL,
>   CODDEP  NCHAR(2)                              NOT NULL,
>   CODPRO  NCHAR(2)                              NOT NULL,
>   CODDIS  NCHAR(2)                              NOT NULL,
>   APEPAT  VARCHAR2(40 BYTE),
>   APEMAT  VARCHAR2(40 BYTE),
>   NOMBRE  VARCHAR2(35 BYTE),
>   FECNAC  NCHAR(8),
>   CODSEX  NCHAR(1),
>   CODGRI  NCHAR(2),
>   CODSTR  NCHAR(1),
>   TIPDOC  NCHAR(1),
>   CONSTRAINT NUMDLETABLEB
>  PRIMARY KEY
>  (NUMDLE)
> )
> ORGANIZATION INDEX
> LOGGING
> TABLESPACE TableSpaceB
> PCTFREE    10
> INITRANS   2
> MAXTRANS   255
> STORAGE    (
>             INITIAL          64K
>             MINEXTENTS       1
>             MAXEXTENTS       2147483645
>             PCTINCREASE      0
>             BUFFER_POOL      DEFAULT
>            )
> NOPARALLEL
> MONITORING;
>
> Both tables have quite the same number of records.
>
> TableA is set in TableSpaceA.
> Primary Key NUMDLETABLEA is set in TableSpaceA.
> TableSpaceA has only TableA and its PK.
>
> CREATE TABLESPACE TableSpaceA DATAFILE
>   '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceDataFileA.ORA'
> SIZE 1864M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
> In O/S Windows: TableSpaceDataFileA.ORA = 1,908,744 KB
> Data Filename = TableSpaceDataFileA.ORA
> Used% = 99.57
> Size = 1864 MB
> Used = 1856.06 MB
> Free = 7.94 MB
> Blocks = 238592
> AutoExtend = On
> Next Extent = 1 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 78.97
>
> What intrigues me is why DataFileTableSpaceB size takes much more
> megabytes than DataFileTableSpaceA size.
>
> TableB is set in TableSpaceB.
> Primary Key NUMDLETABLEB is set in TableSpaceB.
> TableSpaceB has only TableB and its PK.
>
> CREATE TABLESPACE TableSpaceB DATAFILE
>   '...\ORACLE\PRODUCT\10.1.0\ORADATA\OPINT2\TableSpaceB.ORA' SIZE
> 2964M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
> LOGGING
> ONLINE
> PERMANENT
> EXTENT MANAGEMENT LOCAL AUTOALLOCATE
> BLOCKSIZE 8K
> SEGMENT SPACE MANAGEMENT AUTO
> FLASHBACK ON;
>
> In O/S Windows: TableSpaceDataFileB.ORA = 3,035,144 KB
> Data Filename = TableSpaceDataFileB.ORA
> Used% = 99.33
> Size = 2964 MB
> Used = 2944.06 MB
> Free = 19.94 MB
> Blocks = 379392
> AutoExtend = On
> Next Extent = 50 MB
> Max Size = UNLIMITED
> Max Blocks = UNLIMITED
> Status = AVAILABLE
> Fragmentation Index = 100
>
> Both tables are quite the same.
>
> How can I determine why TableB size takes much more megabytes than
> TableA size ?

By examining their segment usage, even looking at block dumps.

There are a number of things that can result in this effect, the most basic being PCTFREE, PCTUSED and NEXT. In fact, you can have a densely packed table, exp/imp it, and the pctfree can make it wind up being larger. Also, deletion can leave a lot of wasted space, which could be reused, or not, depending on your situation, search for high water mark in the docs. Note the difference between your NEXT parameters - a few extensions of the table, then some deletes can lead to big differences. Also note, there can be lots more things besides one table in a tablespace. How exactly did you come up with the used and free that you posted? What is "fragmentation index?" LMT's don't generally fragment, though there could conceivably be issues with autoallocate if you do strange things.

EM has a kind of clunky display on what's in a tablespace, but in can be enlightening with questions like this. You can click on the object name and see where it is.

A common thing missed with imp/exp is the space that it uses when importing - read about the compress parameter carefully in the utilities manual.

There are a few more obscure things that can happen, such as alignment on block boundaries, that can sometimes bite the unwary.

Of course, Windows doesn't know jack about what is inside Oracle data files.

Lots of stuff on the net about how to figure out what is in Oracle tablespaces, but beware of misinformation.

jg

--
@home.com is bogus.
http://www.signonsandiego.com/uniontrib/20081028/news_1b28google.html