You Asked
Hi Tom,
I have one problem which is quite serious
and needs to solved immediately.
I have around 50 tables in a production
database with initial extents as 3mb.
But these are actually small tables which occupy less
than 20k.
How can i reduce the initial
extents of these tables.
The above requirement is quite simple
and can be handled by exporting,
dropping the table , recreate the table
with lesser initial ext and
importing back with IGNORE ERRORS
option.
But what is complicating the requirement
is that these tables have data which
are being referred(have foreign key
constraints) by child tables
so dropping the table and recreating will
not be the best of solutions.
Do you have any ideas on how to handle this??
Pls let me know
Looking forward for your help
Thanks,
Girish V
and Tom said...
You mention no versions so i'll have to guess. I'll guess Oracle8i, release 8.1 first and then 8.0 and before later. In Oracle8i I can do something like:
ops$tkyte@8i> create table p
2 ( x int,
3 constraint p_pk primary key(x)
4 )
5 tablespace users
6 storage ( initial 5m )
7 pctfree 20
8 /
Table created.
ops$tkyte@8i> create table c ( y int references p );
Table created.
ops$tkyte@8i> insert into p values ( 1 );
1 row created.
ops$tkyte@8i> insert into c values ( 1 );
1 row created.
ops$tkyte@8i> insert into c values ( 1 );
1 row created.
ops$tkyte@8i> select initial_extent, pct_free
2 from user_tables
3 where table_name = 'P';
INITIAL_EXTENT PCT_FREE
-------------- ----------
5242880 20
ops$tkyte@8i> alter table p
2 move tablespace users
3 storage ( initial 5k )
4 pctfree 0;
Table altered.
REM must rebuild indexes after a MOVE
ops$tkyte@8i> alter index p_pk rebuild;
Index altered.
ops$tkyte@8i> select initial_extent, pct_free
2 from user_tables
3 where table_name = 'P';
INITIAL_EXTENT PCT_FREE
-------------- ----------
16384 0
If you are in 8.0 or before, you have to do what you describe above with exp/imp or more simply for such small tables:
- use the script below "cons.sql" to generate alter statements that will rebuild the
referential integrity constraints.
- RENAME the table "rename T to T_HOLD"
- create table T tablespace X storage ( ... ) as select * from T_HOLD. Alter table T add constraint .... primary key....
- drop table T_HOLD CASCADE constraints
- use the statements you generated in step 1 to rebuild the foreign keys.
==================== cons.sql ================================================
column fkey format a80 word_wrapped
select
'alter table "' || child_tname || '"' || chr(10) ||
'add constraint "' || child_cons_name || '"' || chr(10) ||
'foreign key ( ' || child_columns || ' ) ' || chr(10) ||
'references "' || parent_tname || '" ( ' || parent_columns || ');' fkey
from
( select a.table_name child_tname, a.constraint_name child_cons_name,
b.r_constraint_name parent_cons_name,
max(decode(position, 1, '"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||substr(column_name,1,30)||'"',NULL))
child_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by a.table_name, a.constraint_name, b.r_constraint_name ) child,
( select a.constraint_name parent_cons_name, a.table_name parent_tname,
max(decode(position, 1, '"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 2,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 3,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 4,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 5,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 6,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 7,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 8,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position, 9,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,10,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,11,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,12,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,13,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,14,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,15,', '||'"'||substr(column_name,1,30)||'"',NULL)) ||
max(decode(position,16,', '||'"'||substr(column_name,1,30)||'"',NULL))
parent_columns
from user_cons_columns a, user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type in ( 'P', 'U' )
group by a.table_name, a.constraint_name ) parent
where child.parent_cons_name = parent.parent_cons_name
and parent.parent_tname = upper('&1')
/
========================= eof =======================================================
when run like:
ops$tkyte@8i> @cons p
old 53: and parent.parent_tname = upper('&1')
new 53: and parent.parent_tname = upper('p')
FKEY
--------------------------------------------------------------------------------
alter table "C"
add constraint "SYS_C0020322"
foreign key ( "Y" )
references "P" ( "X");
it generates the foreign key constraints for all children OF it. You would spool this output to be rerun after rebuilding the table.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment