try to rollup a partition table, it's 300G in total, around 2000 partitions.
the plan is to export old partitions, compress them, then drop the old partitions.
eg, partition p20021018, it's 500MB in dba_segments, while its dump file is 2GB,
this is odd, normally, the dump file size is smaller than their segment size.
as export dump file format is internal, found no related information, not sure what was written into the dump file.
at first I thought indexes=y constraint=y will decreaes the dump file size.
actually the 'y' means export the definition, not the real index.
the second thought is oracle stores the DATE, NUMBER more efficiently, then export dump file.
as there're 58 NUMBER columns and 6 DATE columns in this table.
--the following Doc explains how oracle store DATE, NUMBER internally
. Note:69028.1 how does oracle store the DATE datatype internally
. Note:1031902.6 how does oracle store internal numeric data
--the following describes how to dump a data block, and the SQL function dump
create table familytree (birthday date, whose varchar2(32), money number);
insert into familytree(birthday, whose, money)
values (to_date('2002-03-27','yyyy-mm-dd'), 'bunny', 123.456);
select to_char(birthday,'DD-MON-YYYY HH24:MI:SS') from familytree where whose='bunny';
TO_CHAR(BIRTHDAY,'DD-MON-YYYY
---------------------------------------------------------------------------
27-MAR-2002 00:00:00
SQL> select rowid from familytree where whose='bunny';
ROWID
------------------
AABYhRAAnAAANkiAAA
SELECT dbms_rowid.ROWID_TO_ABSOLUTE_FNO('AABYhRAAnAAANkiAAA','JAS','FAMILYTREE') FROM dual;
DBMS_ROWID.ROWID_TO_ABSOLUTE_F
------------------------------
39
SELECT dbms_rowid.ROWID_BLOCK_NUMBER('AABYhRAAnAAANkiAAA') FROM dual;
DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
55586
alter system dump datafile 39 block 55586;
/oracle/admin/SPMDDB1/udump/spmddb1_ora_24882.trc
look at the 0th column of the 1st row:
col 0: [ 7] 78 66 03 1b 01 01 01 120 102 03 27 01 01 01
actually, use the followin SQL get the same results:
select dump(birthday) from familytree where rownum=1;
DUMP(BIRTHDAY)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,102,3,27,1,1,1
--note, following SQL returns different, it's a to_date, not a date
SELECT dump(to_date('2002-03-27','yyyy-mm-dd')) FROM dual;
DUMP(TO_DATE('2002-03-27','YYY
--------------------------------------------------------------------------------
Typ=13 Len=8: 210,7,3,27,0,0,0,0
select money, dump(money) from familytree where whose='bunny';
Typ=2 Len=5: 194,2,24,46,61
