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

From: Big George <jbeteta_at_gmail.com>
Date: Mon, 27 Oct 2008 10:41:37 -0700 (PDT)
Message-ID: <f74d2854-ede5-475a-bb17-0bde0d78c0ef@d70g2000hsc.googlegroups.com>

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 ? Received on Mon Oct 27 2008 - 12:41:37 CDT