Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: resizing initial extent downwards

Re: resizing initial extent downwards

From: David Fitzjarrell <oratune_at_aol.com>
Date: Tue, 28 Nov 2000 15:00:46 GMT
Message-ID: <900haq$qih$1@nnrp1.deja.com> 






In our last gripping episode Steve Salvemini
<steve.salvemini_at_adelaide.edu.au> wrote:


> What we'd like to do is delete 90% of a table (where the initial

 extent


> has been extended to take up the whole table ie. 250Mb), and reclaim

> that space.

>

> We initially thought that by exporting the table and re-importing it,

> the initial

> extent would be set to the actual size of the table. It doesn't

 though,


> it keeps it at 250Mb, where the data now only needs 20Mb as an initial

> extent.

>

> We are doing this same process for some 200+ tables so a generic

> solution would be best.

>

> Any ideas?

>

> Thanks

>

> Steve

>




My first thought would be to implement uniform extents for a given
tablespace.  That being said this script can help generate the
necessary CREATE TABLE statements:



set head off pages 0 serveroutput on lines 80
set feedback off verify off



declare

	col_name	char(30);
	prev_col_name	char(30);
	col_type	user_tab_columns.data_type%type;
	col_len		user_tab_columns.data_length%type;
	col_prec	user_tab_columns.data_precision%type;
	col_scale       user_tab_columns.data_scale%type;
	tbl_name	user_tab_columns.table_name%type;
	prev_tbl_name	user_tab_columns.table_name%type:=null;
        tblspce_name    user_tables.tablespace_name%type;
	pctfre		user_tables.pct_free%type;
	pctusd		user_tables.pct_free%type;
	notnl		char(8);
	pct_inc		user_tables.pct_increase%type;
	init_ext	user_tables.initial_extent%type;
	next_ext	user_tables.next_extent%type;
	cursor get_tbl_info is
	select c.table_name, c.column_name, c.data_type, c.data_length,
	       c.data_precision, c.data_scale,
	       d.tablespace_name,
	       decode(c.nullable,'Y','        ','N','NOT NULL'),
	       '4194304', '4194304', d.pct_increase, d.pct_free,
	       (100 - d.pct_free) pct_used
	from user_tab_columns c, user_tables d
	where
	c.table_name = upper('&&1')
        and d.table_name = c.table_name
	and d.table_name not like 'SYS%'
	ORDER BY
	c.table_name,
	c.column_id;
begin
	dbms_output.enable(1000000);
	open get_tbl_info;
	loop
	fetch get_tbl_info into tbl_name,
				col_name, col_type, col_len, col_prec,
col_scale, tblspce_name,
				notnl, init_ext, next_ext, pct_inc,
pctfre, pctusd;
	exit when get_tbl_info%notfound;
	if tbl_name != prev_tbl_name or prev_tbl_name is null then
		if prev_col_name is not null then
			dbms_output.put_line(prev_col_name||')');
			dbms_output.put_line
('tablespace '||tblspce_name);
			dbms_output.put_line('pctfree '||pctfre);
			dbms_output.put_line('pctused '||pctusd);
			dbms_output.put('storage( initial '||init_ext);
			dbms_output.put(' next '||next_ext);
			dbms_output.put_line('
pctincrease '||pct_inc||');');
		end if;
		dbms_output.put_line('-- ');
		dbms_output.put_line('-- '||tbl_name);
		dbms_output.put_line('-- ');
		dbms_output.put_line('drop table '||tbl_name||';');
		dbms_output.put_line('create table '||tbl_name);
		dbms_output.put_line('(');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and
col_type <> 'NUMBER' and col_type <> 'DATE' and notnl = 'NOT NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_len||') '||notnl||',');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and
col_type <> 'NUMBER' and col_type <> 'DATE' and notnl = '        ' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_len||'),');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and


col_type = 'NUMBER' and notnl = 'NOT NULL' and col_prec is not null and
col_scale is not null then

		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_prec||','||col_scale||') '||notnl||',');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and


col_type = 'NUMBER' and notnl <> 'NOT NULL' and col_prec is not null
and col_scale is not null then

		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_prec||','||col_scale||'),');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and


col_type = 'NUMBER' and notnl = 'NOT NULL' and col_prec is null and
col_scale is null then

		dbms_output.put_line(prev_col_name||'
	'||col_type||' '||notnl||',');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and


col_type = 'NUMBER' and notnl <> 'NOT NULL' and col_prec is null and
col_scale is null then

		dbms_output.put_line(prev_col_name||'
	'||col_type||',');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and
col_type = 'DATE' and notnl = 'NOT NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||' '||notnl||',');
	end if;
	if col_name != prev_col_name and tbl_name = prev_tbl_name and
col_type = 'DATE' and notnl <> 'NOT NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||',');
	end if;
	prev_tbl_name := tbl_name;
	prev_col_name := col_name;
	end loop;
	if col_type <> 'NUMBER' and col_type <> 'DATE' and notnl = 'NOT
NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_len||') '||notnl||')');
	end if;
	if col_type <> 'NUMBER' and col_type <> 'DATE' and notnl
= '        ' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_len||'))');
	end if;
	if col_type = 'NUMBER' and notnl = 'NOT NULL' and col_prec is
not null and col_scale is not null then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_prec||','||col_scale||') '||notnl||')');
	end if;
	if col_type = 'NUMBER' and notnl <> 'NOT NULL' and col_prec is
not null and col_scale is not null then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'('||col_prec||','||col_scale||'))');
	end if;
	if col_type = 'NUMBER' and notnl = 'NOT NULL' and col_prec is
null and col_scale is null then
		dbms_output.put_line(prev_col_name||'
	'||col_type||' '||notnl||')');
	end if;
	if col_type = 'NUMBER' and notnl <> 'NOT NULL' and col_prec is
null and col_scale is null then
		dbms_output.put_line(prev_col_name||'
	'||col_type||'))');
	end if;
	if col_type = 'DATE' and notnl = 'NOT NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||' '||notnl||')');
	end if;
	if col_type = 'DATE' and notnl <> 'NOT NULL' then
		dbms_output.put_line(prev_col_name||'
	'||col_type||')');
	end if;
	dbms_output.put_line('tablespace '||tblspce_name);
	dbms_output.put_line('pctfree '||pctfre);
	dbms_output.put_line('pctused '||pctusd);
	dbms_output.put('storage( initial '||init_ext);
	dbms_output.put(' next '||next_ext);
	dbms_output.put_line(' pctincrease '||pct_inc||');');


end;


/


exit



Save the above to a file named, for want of a better name,
table_create.sql (or anything else you may wish to name it).  Using the
suggested name create a script, via SQL*Plus, to invoke this script for
each table you need to resize:



select '@table_create '||table_name


from user_tables



spool resize_tbls.sql


/


spool off



Now edit the resize_tbls.sql script to spool the output to a single
file:



spool new_extents.sql


@table_create ...


@table_create ...


spool off



Run the resize_tbls.sql script; you will generate the new_extents.sql
script containing DROP TABLE and CREATE TABLE statements for each table:


--
-- CDR
--
drop table CDR;
create table CDR
(
CALLDETAILID                    VARCHAR2(20),
USERID                          VARCHAR2(20),
ACCOUNDID                       NUMBER(11,0),
TRANSTYPE                       DATE,
STARTTIME                       DATE,
ENDTIME                         VARCHAR2(20),
DNIS                            NUMBER(11,0),
CALLSTATUS                      NUMBER(11,0),
TRUNK                           VARCHAR2(20),
ANI                             VARCHAR2(20))
tablespace VM_CDR4_DATA
pctfree 10
pctused 90
storage( initial 4194304 next 4194304 pctincrease 50);

The initial extent and next extent sizes have been initially set to 4
Meg (4194304 bytes) to create uniform extent sizes.  This value can be
modified in the cursor used to return the information required to
create the table so you can set your initial and next values as you
wish, even as a percentage of the current value:

        --
        -- Fixed percentage
        --
	cursor get_tbl_info is
	select c.table_name, c.column_name, c.data_type, c.data_length,
	       c.data_precision, c.data_scale,
	       d.tablespace_name,
	       decode(c.nullable,'Y','        ','N','NOT NULL'),
	       d.initial_extent*20/100, d.next_extent*20/100,
               d.pct_increase, d.pct_free,
	       (100 - d.pct_free) pct_used
	from user_tab_columns c, user_tables d
	where
	c.table_name = upper('&&1')
        and d.table_name = c.table_name
	and d.table_name not like 'SYS%'
	ORDER BY
	c.table_name,
	c.column_id;


        --
        -- Variable percentage
        --
        -- Value is passed in as second parameter with
        -- table name
        --
        -- @table_create CDR 40
        --
        -- Create table CDR with extents 40 percent of current
        -- size
        --
	cursor get_tbl_info is
	select c.table_name, c.column_name, c.data_type, c.data_length,
	       c.data_precision, c.data_scale,
	       d.tablespace_name,
	       decode(c.nullable,'Y','        ','N','NOT NULL'),
	       d.initial_extent*&&2/100, d.next_extent*&&2/100,
               d.pct_increase, d.pct_free,
	       (100 - d.pct_free) pct_used
	from user_tab_columns c, user_tables d
	where
	c.table_name = upper('&&1')
        and d.table_name = c.table_name
	and d.table_name not like 'SYS%'
	ORDER BY
	c.table_name,
	c.column_id;

I hope this helps.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.


Received on Tue Nov 28 2000 - 09:00:46 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US