Object type of Foreign Key in DBMS_METADATA.GET_DDL

It is REF_CONSTRAINT  for referential constraint, not CONSTRAINT

flashback db log size 3 times of AL size

interesting finding.

> du -sk *
10786344        archivelog
30174024        flashback
> ls -lR
total 48
drwxrwx---  50 oracle1    dba1          8192 Oct  1 17:54 archivelog
drwxrwx---   2 oracle1    dba1         16384 Oct  2 00:06 flashback

...

./archivelog/2013_10_01:
total 21572480
-rw-r-----   1 oracle1    dba1       1854297088 Oct  1 17:55 o1_mf_1_166081_94o6yvnp_.arc
-rw-r-----   1 oracle1    dba1       1819378688 Oct  1 17:56 o1_mf_1_166082_94o71ffv_.arc
-rw-r-----   1 oracle1    dba1       1821615104 Oct  1 17:58 o1_mf_1_166083_94o75onb_.arc
-rw-r-----   1 oracle1    dba1       1855338496 Oct  1 18:35 o1_mf_1_166084_94o9cf4t_.arc
-rw-r-----   1 oracle1    dba1       1833089024 Oct  1 19:15 o1_mf_1_166085_94oco8mm_.arc
-rw-r-----   1 oracle1    dba1       1861282816 Oct  1 19:30 o1_mf_1_166086_94odl28d_.arc

./flashback:
total 60348016
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94dzgovx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94dzgzbj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94dzh8rz_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94dzhlfl_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94dzhw0k_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94dzj69y_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:16 o1_mf_94dzjj9z_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:17 o1_mf_94dzjt58_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:17 o1_mf_94dzk3nv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:18 o1_mf_94dzkfrn_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:18 o1_mf_94dzkn9c_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94f001p7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:25 o1_mf_94f00c9r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  2 00:06 o1_mf_94f00nyx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f00vkj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f01c1v_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f01njr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f01v26_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f024kh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f02c0j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f02nm9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94f02v7w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f034tl_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f03ck6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f03oc2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f03vxc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f045dx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f04cwq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:00 o1_mf_94f04ll8_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94f04w2z_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94f052oc_.flb
-rw-r-----   1 oracle1    dba1       49594368 Oct  1 19:01 o1_mf_94gndrfg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94lpmmx3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94lpmqnr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94lpmyh1_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94lpn5ls_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:01 o1_mf_94lpnd9r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpnlxw_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpnwoh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpo3cj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpo9wk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpokfj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpor3v_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpoyvc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpp8kv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:02 o1_mf_94lpphnw_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lppphc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lppxnb_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpq7hj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqbmy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqg40_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqj9t_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqk7j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqn44_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:03 o1_mf_94lpqoh2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94lpqr3t_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94lpqt1n_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94lqbnrh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94ls3hls_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94ls3pd8_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94ls3wyr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:04 o1_mf_94ls43y6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94ls4c9p_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94ls4kwp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94ls95x6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94lsyr3h_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94ltpf7t_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94lttdcg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:05 o1_mf_94lttpcp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltww9c_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltx68t_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltxp22_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltxzop_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94lty9d9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltyj7x_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94ltymtx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:06 o1_mf_94lv20oq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv2bh4_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv2k4o_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv2trd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv31g4_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv3c1p_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv3no8_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv3vs9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:07 o1_mf_94lv45r3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv4hjq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv4s86_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv52vy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv5dlj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv5mgy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv5wyv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:08 o1_mf_94lv66k9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lv8rr2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lv8zj7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lv965y_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lvbc0w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lvbtlh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lvc4hj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lvcg2m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:09 o1_mf_94lvcnk7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvcv3r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvd1sm_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvd8d9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvdh8m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvdp5q_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvdwxc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvfdn1_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:10 o1_mf_94lvmt24_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvsdob_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvsmct_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvsxgk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvt4rm_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvtgx7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lvtotj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:11 o1_mf_94lw81xf_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lw8cg2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lw8hk6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lw8sg1_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lx3t9c_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lx3y66_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lx8lo0_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:12 o1_mf_94lx8p0g_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx8pd0_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx8trz_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx8yg6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx92b8_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx95xp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lx9hyv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lxdt6s_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:13 o1_mf_94lxf42m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94ngfghc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94ngfo8n_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94ngfvqx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94o6z4ho_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94o6z9dy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94o6zjvm_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7h8fh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7k1cj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7kc2m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7kv7r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7lqm1_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7m4bt_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7mkcj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:15 o1_mf_94o7n14o_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:16 o1_mf_94o7o9gk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:16 o1_mf_94o7osd3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:16 o1_mf_94o7pbdl_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:16 o1_mf_94o7pt7w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:17 o1_mf_94o7r52m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:17 o1_mf_94o7rnwg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:18 o1_mf_94o7s7lk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:18 o1_mf_94o7t0sg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:19 o1_mf_94o7tx2m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:19 o1_mf_94o7v72h_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:19 o1_mf_94o7vmss_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:19 o1_mf_94o7w3yc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:20 o1_mf_94o7wjoh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:20 o1_mf_94o7x0cd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:20 o1_mf_94o7xf51_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:21 o1_mf_94o7xqdy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:21 o1_mf_94o7y7cj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:21 o1_mf_94o7yn2j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:22 o1_mf_94o7z3po_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:22 o1_mf_94o7zmjj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:22 o1_mf_94o800b0_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:22 o1_mf_94o80jcy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:22 o1_mf_94o80xq9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:23 o1_mf_94o81fmy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:23 o1_mf_94o81xc5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94o82b47_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94o8363v_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94o83orx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94o842fg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:24 o1_mf_94o84d43_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:25 o1_mf_94o88khc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  2 09:13 o1_mf_94o8p9nx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  2 11:09 o1_mf_94o8z5go_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o8z9fd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o8zdx0_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o8zjny_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o8znpq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o8zs5j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o9007o_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o906yr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:29 o1_mf_94o90fpd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o90qjk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o91171_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o91fyk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o91qqg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o9220s_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:30 o1_mf_94o92ct3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o92oh3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o92w6n_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o93r23_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o93ysb_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o945cp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:31 o1_mf_94o94h3k_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:32 o1_mf_94o94p3y_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:33 o1_mf_94o94zs5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:33 o1_mf_94o96q5k_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:49 o1_mf_94o970vx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:49 o1_mf_94o977r8_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:49 o1_mf_94ob66mw_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:49 o1_mf_94ob6g0m_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob6ntq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob6rdl_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob6zmw_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob73by_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob7c3w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob7grs_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob7p29_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob7wpp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob80fw_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:50 o1_mf_94ob872w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob8bng_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob8kdr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob8o5j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob8vxm_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob8zrd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob96bs_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob9f12_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob9jod_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob9q94_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94ob9z2t_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:51 o1_mf_94obb2qg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obb9ol_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obbfh4_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obbn36_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obbqt5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obbydv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obc21r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obc8sx_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obcddk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obcm0x_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obcplv_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obcxbl_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:52 o1_mf_94obd12f_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obd7ob_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obdgco_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obdl6j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obdrrb_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obdwm9_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obf3hj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obf7kz_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obfc68_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obfktg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obfokq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:53 o1_mf_94obfw6l_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obfzqk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obg6f7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obg9xq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obgjg3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obgn25_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obgqr1_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obgyov_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obh284_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obh605_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obhdlp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obhj7b_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obhmt2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:54 o1_mf_94obhtfb_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obhy5w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obj4tm_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obj8kq_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94objd7n_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94objj4v_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94objpr6_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94objtdb_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obk112_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obk4qd_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obk8nj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obkh5c_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obklv2_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:55 o1_mf_94obkpm3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obkxc5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obl0xc_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obl7sk_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94oblcdp_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94oblkwg_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94oblohn_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obls29_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obm00r_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obm3lr_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obmf2s_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:56 o1_mf_94obmn5q_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obmrhy_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obmw4j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obn2t3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obn6d7_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obn9wh_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obnjhn_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obnn70_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obntvo_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obnyck_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obo1y3_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obo8hf_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:57 o1_mf_94obod7w_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obolt4_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obopp5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obotcn_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obp12j_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obp4ln_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obpc3s_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obpgvs_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obpljj_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obpp72_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obpwto_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obq0kz_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:58 o1_mf_94obq44n_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 18:59 o1_mf_94obqbp5_.flb
-rw-r-----   1 oracle1    dba1       99188736 Oct  1 19:14 o1_mf_94oco7k7_.flb
-rw-r-----   1 oracle1    dba1       49594368 Oct  1 19:15 o1_mf_94ocoh4r_.flb
-rw-r-----   1 oracle1    dba1       49594368 Oct  2 10:19 o1_mf_94owqsw7_.flb

how to set user password unexpire?

Actually, we can retain the current password by " alter user  identified by values ' xxxx' " , while changing the status from expired to open.  In 11g, the hashed password can be found in SYS.USER$.


Ref:
http://stackoverflow.com/questions/1766445/oracle-how-to-set-user-password-unexpire

How to spot duplicate datafiles

Oracle Expert » How to spot duplicate datafiles

 

select *  from (
    select  tablespace_name, fullpath, filename, count(filename) over (partition by filename)  freq
    from  ( select tablespace_name, file_name fullpath, substr(file_name, instr(file_name,'/',-1)+1)  filename from dba_data_files )
 )
where freq > 1;

 

 

set arraysize [SQL*Plus]

Increase parameter COMPATIBLE

Here are change info found in alert.log when bounce the database.

...

ALERT: Compatibility of the database is changed from 10.2.0.2.0 to 11.2.0.2.0.

Increased the record size of controlfile section 4 to 520 bytes
Control file expanded from 2274 blocks to 2320 blocks
Increased the record size of controlfile section 14 to 200 bytes
Control file expanded from 2320 blocks to 2342 blocks
Increased the record size of controlfile section 16 to 736 bytes
Control file expanded from 2342 blocks to 2362 blocks
Increased the record size of controlfile section 20 to 928 bytes
Control file expanded from 2362 blocks to 2382 blocks
Increased the record size of controlfile section 21 to 124 bytes
Control file expanded from 2382 blocks to 2388 blocks
Increased the record size of controlfile section 22 to 900 bytes
The number of logical blocks in section 22 remains the same

 

...

special characters in sql*plus

the following characters are replaces by:
 @ => ORACLE_SID
 ? => ORACLE_HOME

SQL> spool test_@.log
SQL> prompt hi
hi
SQL> spool off
SQL> !ls -rlt test*
-rw-r--r--   1 oravcms    oravcms        136 Mar  7 14:32 test_VCMST.log

Oracle Expert » How to spot duplicate datafiles

alter system register to speed up dynamic registration

【LISTENER】使用“alter system register;”解决动态监听注册缓慢问题 - secooler 快乐的DBA - ITPUB个人空间 - powered by X-Space

more redo size generated when undo tablespace is insufficient and rollback as result

oracle新人关于redo和undo的疑惑 - Oracle专题深入讨论 - ITPUB论坛 - it168旗下专业技术社区

tnsping response time

Should I use TNSPING to test my oracle net performance ?

 

Typically when TNSPING times go up in Dedicated Server configuration, it is because the system is hitting a high level of load and the listener is having to wait for a process to fork and execute the oracle dedicated server.The TNSPING program sends a packet to the listener,which goes into it’s listening queue.If there were also connect requests in the queue, then the listener will handle each request (including the tns “ping”) in the order they were received per second. If those connect requests take time, then it will take time to process the ping.TNSPING should never be used to test network performance. TNSPING’s only function is to send a connect Packet (NSPTCN) to the listener, Listener replies with a refuse Packet (NSPTRF) and a round trip time is computed. A slow TNSPING time could be anything from poor DNS resolution to a slow network to a busy listener to a busy server.

If connections are going fine ,We should not be worried about the tnsping response time.

How to disable 10g recycle bin (to avoid ORA-01658)

Developer feedback that she is not able to create a table, regarless of 13GB free space in the tablespace.

The error message is :

ORA-01658: unable to create INITIAL extent for segment in tablespace.

Problem solved immediately after I purged recyclebin from my 2nd feeling.


Finally , I found :
in 10g , if you have database running with recyclebin=on  for some period and you have objects create & drop in those tablespaces.
Due to some reason, e.g restore to another location with recyclebin = off, or you decided to turn it off. Howerver, the dropped objects  when recyclebin was ON , will remain in the recyclebin even if we set the recyclebin parameter to OFF.  Hence, the space shown in dba_free_space is not swapped out, cuased the error.

Below is my test.

SYS@ODSPRX2> show parameter recyclebin;

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
recyclebin                           string      on                             



SYS@ODSPRX2> create tablespace tbs1 datafile '/software/oraods/labs/recyclebin/tbs1.dbf' size 5m
  2  extent management local uniform size 512k
  3  segment space management auto;

Tablespace created.

@> conn / as sysdba
Connected.


SYS@ODSPRX2> create user liqy identified by liqyliqy default tablespace tbs1;

User created.

SYS@ODSPRX2> grant dba to liqy;

Grant succeeded.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> create table t1 (f1 number)  ;

Table created.

LIQY@ODSPRX2> create table t2 (f1 number)  storage(initial 512k);

Table created.

LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
     BYTES                                                                      
----------                                                                      
T1                                                                              
    524288                                                                      
                                                                                
T2                                                                              
    524288                                                                      
                                                                                

LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

     BYTES                                                                      
----------                                                                      
   3670016                                                                      

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             7                                                                  

LIQY@ODSPRX2> alter table t2 allocate extent (size 3670016);

Table altered.

LIQY@ODSPRX2> select bytes from user_free_space where tablespace_name='TBS1';

no rows selected

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1 


LIQY@ODSPRX2> select segment_name, bytes from dba_segments where tablespace_name='TBS1';

SEGMENT_NAME                                                                    
--------------------------------------------------------------------------------
     BYTES                                                                      
----------                                                                      
T1                                                                              
    524288                                                                      
                                                                                
T2                                                                              
   4194304                                                                      
                                                                                

LIQY@ODSPRX2> drop table t1;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.


LIQY@ODSPRX2> drop table t3;

Table dropped.

LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> conn / as sysdba
Connected.
SYS@ODSPRX2> alter system set recyclebin=off;

System altered.

SYS@ODSPRX2> conn liqy/liqyliqy
Connected.
LIQY@ODSPRX2> select bytes/512/1024 from user_free_space where tablespace_name='TBS1';

BYTES/512/1024                                                                  
--------------                                                                  
             1                                                                  

LIQY@ODSPRX2> create table t3 (f1 number);
create table t3 (f1 number)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS1 


LIQY@ODSPRX2> purge recyclebin;

Recyclebin purged.

LIQY@ODSPRX2> create table t3 (f1 number);

Table created.

LIQY@ODSPRX2> spool off
SYS@ODSPRX2> drop tablespace tbs1 including contents and datafiles;

Tablespace dropped.

SYS@ODSPRX2> spool off

But in 11.2.0.2, the dropped objects will be swapped out even with recyclebin=off.

BTW, in 11gr2 we can't use 'alter system' to change value of recyclebin, while in 10g we can change it on the fly. The oracle document is not correct.

database capacity planning for running database

storage administrator may not know the annual growth in database, while the database may keep growing. As DBA we need to regularly , say yearly, review the growth of critical database, before it is too late to realize that we have no space to grow in SAN.


Below are few areas to drill down for careful review. 

    1. Identify main contributers (tablespaces) to the growth. To achieve this, ideally you have job to record the tablespace used, total size daily , or simply can based the data file creation timestamp in dba_data_files.  Create a spreadshee to calculate space needed for coming two years, using dimension tablespace  and mount point name.



  2. Return space to disk by shrink down data files of over-allocated tablespace. Or even you can drop unused tablespace.
 
 
  3.  Check if any housekeeping job is not paused accidentally. If there is big table in the tablespace and keeps growing, check with application if housekeep can be taken.  
  
  4. Check tablespace with uniform extent size , especially for uniform size >= 10MB, make sure no space wasted due to forget to consider 8x8k or 4x32k header overhead for each data file.

fix the database date

SQL> select sysdate from dual;

SYSDATE
---------
03-JAN-11

SQL> !date
Tue May 31 09:33:05 SST 2011

SQL> alter system set fixed_date=none;

System altered.

SQL> select sysdate from dual;        

SYSDATE
---------
31-MAY-11

datafile overhead of tablespace using uniform extent size

    Note that there is datafile overhead for tablespaces with uniform size extent allocation .

     i.e, eight 8k blocks used for 8k block_size tablespace and  four 32k blocks for 32k block size tablespace.

    eg. Add a 1000MB datafile to existing 8k block_size tablespace with 100MB uniform extent size tablespace in CUSTPA, the right size should be 1MB*1000+8k*8 =1000Mb+64Kb=1048641536 bytes. 

     -- If we specify right "size 1000m", there will be only 9 x 100Mb extents available, 1 extent with 100Mb is used for overhead (wasted).  

     -- If specify "size 1G" , it is 1024Mb , we waste (24Mb- 64kb). The datafile size should be (round to integer times of uniform extent size + datafile overhead). To make it easy, just plus 1MB for each data file in the tablespace.

    Please take note when extend tablespaces. 

   SYS > show parameter block_size

NAME                                 TYPE        VALUE                          
------------------------------------ ----------- ------------------------------ 
db_block_size                        integer     32768                          

SYS > create tablespace test datafile '/ods001/oradata/ODSDMS/test.dbf' size 100m
  2  extent management local uniform size 1m;

Tablespace created.


SYS > select file#, bytes ,creation_time from v$datafile where file#=27;

     FILE#      BYTES CREATION_                                                 
---------- ---------- ---------                                                 
        27  104857600 27-MAY-11                                                 


SYS > create user test identified by test;

User created.

SYS > grant dba to test;

Grant succeeded.

SYS > alter user test default tablespace test;

User altered.

SYS > connect test/test;
Connected.
TEST > desc dba_free_space
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLESPACE_NAME                                    VARCHAR2(30)
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

TEST > set pages 1000
TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   27          5  103809024       3168      
          27                                                                    
                                                                                

TEST > select 103809024/1048576 from dual;

103809024/1048576                                                               
-----------------                                                               
               99                                                               

TEST > prompt 1mb is "missing"
1mb is "missing"
TEST > prompt 1mb is the uniform size
1mb is the uniform size
TEST > desc dba_extents;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 SEGMENT_NAME                                       VARCHAR2(81)
 PARTITION_NAME                                     VARCHAR2(30)
 SEGMENT_TYPE                                       VARCHAR2(18)
 TABLESPACE_NAME                                    VARCHAR2(30)
 EXTENT_ID                                          NUMBER
 FILE_ID                                            NUMBER
 BLOCK_ID                                           NUMBER
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 RELATIVE_FNO                                       NUMBER

TEST > select * from dba_extents where file_id=27;

no rows selected

TEST > prompt not allocated yet
not allocated yet
TEST > select 32*1024*8 overhead_bytes from dual;

OVERHEAD_BYTES                                                                  
--------------                                                                  
        262144                                                                  

TEST > select 104857600+262144 from dual;

104857600+262144                                                                
----------------                                                                
       105119744                                                                

TEST > alter database datafile '/ods001/oradata/ODSDMS/test.dbf' resize 105119744 ;

Database altered.

TEST > select * from dba_free_space where file_id=27;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   27          5  104857600       3200      
          27                                                                    
                                                                                

TEST > prompt 100MB available for allocation
100MB available for allocation

TEST > alter tablespace test add datafile '/ods001/oradata/ODSDMS/test2.dbf' size 10485760;

Tablespace altered.

TEST > select *  from v$datafile where file#=28;

     FILE# CREATION_CHANGE# CREATION_        TS#     RFILE# STATUS  ENABLED     
---------- ---------------- --------- ---------- ---------- ------- ----------  
CHECKPOINT_CHANGE# CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE#       
------------------ --------- --------------------- --------- ------------       
LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TI      BYTES     BLOCKS        
--------- --------------- -------------- --------- ---------- ----------        
CREATE_BYTES BLOCK_SIZE                                                         
------------ ----------                                                         
NAME                                                                            
--------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET                                                        
---------- -------------                                                        
AUX_NAME                                                                        
--------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NON                                                   
------------------- ---------                                                   
        28         93782698 27-MAY-11          8         28 ONLINE  READ WRITE  
          93782699 27-MAY-11                     0                              
                        0              0             10485760        320        
    10485760      32768                                                         
/ods001/oradata/ODSDMS/test2.dbf                                                
         0         32768                                                        
NONE                                                                            
                  0                                                             
                                                                                

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    
                                                                                

TEST > select 9437184/1048576 from dual;

9437184/1048576                                                                 
---------------                                                                 
              9                                                                 

                                                                              


TEST > select 32*1024*2+10485760 from dual;

32*1024*2+10485760                                                              
------------------                                                              
          10551296                                                              

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10551296;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    
                                                                                
 -- -- 9 x 1MB extents

TEST > select 32*1024*3+10485760 from dual;

32*1024*3+10485760                                                              
------------------                                                              
          10584064                                                              

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10584064 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5    9437184        288      
          28                                                                    

-- still 9 x 1MB extents                                                                                

TEST > select 32*1024*4+10485760 "extra4blocks" from dual;

extra4blocks                                                                    
------------                                                                    
    10616832                                                                    

TEST > alter database datafile '/ods001/oradata/ODSDMS/test2.dbf' resize  10616832 ;

Database altered.

TEST > select * from dba_free_space where file_id=28;

TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS      
------------------------------ ---------- ---------- ---------- ----------      
RELATIVE_FNO                                                                    
------------                                                                    
TEST                                   28          5   10485760        320  


-- 10 x 1MB extents now