'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\TableSpa ceDataFileA.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\TableSpa ceB.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 ?

Thanks a lot!

Comments embedded.

On Oct 27, 4:48*pm, Big George <jbet... (AT) gmail (DOT) com> wrote:
They may not have the same data, though, and the data in TableB may be
chained/migrated causing Oracle to consume a larger number of data
blocks to store the information.

I am not a fan of autoextend, although I understand it has its place.
Setting autoextend without restricting the file size, though, is
simply asking for trouble. If you're planning on using autoextend you
should set a MAXSIZE to something far less than UNLIMITED.

SQL> @?/rdbms/admin/utlchain

Table created.

SQL> analyze table tableb list chained rows;

Table analyzed.

SQL> select owner, table_name, count(*)
2 from chained_rows
3 group by owner, table_name;

....

You may be surprised at what you find if you do this.

But the data in them is not.

I've shown you, in code above. Run that, as I've shown you, and see
what 'falls out'.



David Fitzjarrell

Comments embedded.

On Oct 27, 4:48*pm, Big George <jbet... (AT) gmail (DOT) com> wrote:
They may not have the same data, though, and the data in TableB may be
chained/migrated causing Oracle to consume a larger number of data
blocks to store the information.

I am not a fan of autoextend, although I understand it has its place.
Setting autoextend without restricting the file size, though, is
simply asking for trouble. If you're planning on using autoextend you
should set a MAXSIZE to something far less than UNLIMITED.

SQL> @?/rdbms/admin/utlchain

Table created.

SQL> analyze table tableb list chained rows;

Table analyzed.

SQL> select owner, table_name, count(*)
2 from chained_rows
3 group by owner, table_name;

....

You may be surprised at what you find if you do this.

But the data in them is not.

I've shown you, in code above. Run that, as I've shown you, and see
what 'falls out'.



David Fitzjarrell

Comments embedded.

On Oct 27, 4:48*pm, Big George <jbet... (AT) gmail (DOT) com> wrote:
They may not have the same data, though, and the data in TableB may be
chained/migrated causing Oracle to consume a larger number of data
blocks to store the information.

I am not a fan of autoextend, although I understand it has its place.
Setting autoextend without restricting the file size, though, is
simply asking for trouble. If you're planning on using autoextend you
should set a MAXSIZE to something far less than UNLIMITED.

SQL> @?/rdbms/admin/utlchain

Table created.

SQL> analyze table tableb list chained rows;

Table analyzed.

SQL> select owner, table_name, count(*)
2 from chained_rows
3 group by owner, table_name;

....

You may be surprised at what you find if you do this.

But the data in them is not.

I've shown you, in code above. Run that, as I've shown you, and see
what 'falls out'.



David Fitzjarrell

Comments embedded.

On Oct 27, 4:48*pm, Big George <jbet... (AT) gmail (DOT) com> wrote:
They may not have the same data, though, and the data in TableB may be
chained/migrated causing Oracle to consume a larger number of data
blocks to store the information.

I am not a fan of autoextend, although I understand it has its place.
Setting autoextend without restricting the file size, though, is
simply asking for trouble. If you're planning on using autoextend you
should set a MAXSIZE to something far less than UNLIMITED.

SQL> @?/rdbms/admin/utlchain

Table created.

SQL> analyze table tableb list chained rows;

Table analyzed.

SQL> select owner, table_name, count(*)
2 from chained_rows
3 group by owner, table_name;

....

You may be surprised at what you find if you do this.

But the data in them is not.

I've shown you, in code above. Run that, as I've shown you, and see
what 'falls out'.



David Fitzjarrell