RE: Re-create 7.3 DB, new blksize, init/next/pct; import; OOPS im

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_000_01BFAFEA.A70A5A00
Content-Type: text/plain;

        charset="iso-8859-1"

Hi Bob and gnu list ...

Test. Just kidding.

Since you're on WinNT for Oracle (as am I) I'll assume that you're GUI inclined.
(here come the flames ...)

here's one method:

Download TOAD at the usual location: http://www.toadsoft.com/toadfree.zip Install.

connect as the owner of the schema (naturally). Select the Menu Database >> Export >> Table Scripts Click the Select All button.
Click the OK button.
You are now in the Table script creation section Select the options that you would like.
You might elect to not include indexes, and just create the tables. You might elect to include the indexes, but disable them prior to import. Click on the Options tab.
Save to File - enter a path or click on the '...' browse button.

After saving a backup copy, turn your favorite editor loose on it to replace the storage parameters (assuming global storage defaults) to your desired defaults.
Then make your table-specific storage parameters directly to the script.

Export the user whose tables you wish to modify the storage parameters. Destroy the user ...
Re-create the user ...
Execute the table-create script.
Import the user's objects, IGNORE=Y.
compute stats, etc ...

Examine the table storage params.

here's another method:

adapt the attached script (from the O'Reilly Oracle Script book) to map to a table that you create that contains the table-specific storage parameters that you want (overrides).
for tables which do not have overrides, have it either use existing or default storage parameters.

hth,

Paul

Paul Drake
Digest only here at work ...
(about to find out about the policy of attaching scripts mailed to the
general list)

-----Original Message-----
From: Bob Bryla [mailto:rjbryla_at_mhtc.net] Sent: Wednesday, April 26, 2000 7:57 PM
To: Multiple recipients of list ORACLE-L Subject: Re-create 7.3 DB, new blksize, init/next/pct; import; OOPS imports old

Hello,

Re-created a DB last weekend (7.3.4 under NT) larger blocksize, better segment defaults
when creating the
tablespaces, per Oracle tool author's (third party OO layer) recommendations.

Took previous night's export, imported, OOPS, the old init/next/pctincrease/pctfree/pctused were imported along with the tables, this is BAD
(and

they have found out the hard way).

I see a few options at a high level, which one(s) make sense: (a) export again; drop all
indexes/tables/constraints (about 224 tables total), import twice (?) once with the DDL
only
to create the tables and indexes,
import again with the tables already there... (b) create new tables with same names +
_NEW, CREATE TABLE ... SELECT AS which should default the new tables' params to the
tablespace defaults, then rename orig to _OLD, rename good ones, drop _OLD ones or (c) ???

One of the (few) good things about the tool they're using is that once the tables
are there, the tool will automatically generate and run the DDL to create the constraints
and indexes.

And any scripts that are out there to automate something like this would be appreciated...

Thanks.
BB

-- 
Author: Bob Bryla
  INET: rjbryla_at_mhtc.net


------_=_NextPart_000_01BFAFEA.A70A5A00
Content-Type: application/octet-stream;
	name="CRTABLES.SQL"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
	filename="CRTABLES.SQL"
Content-Location: ATT-0-F6687601891BD411A33C00A0C9D7D980-C
	RTABLES.SQL

rem $DBA/crtables.sql
rem
rem This script generates another script that will include all the =
CREATE
rem TABLE statements for those in the database (except for the SYS =
user).
rem
rem This script must be run by a user with the DBA role under Oracle =
7.2 or
rem later.  (Otherwise the FREELISTS and FREELIST GROUPS clauses will =
have
rem to be omitted).
rem
rem The dbms_output package must be available.
rem
rem Last Change 08/18/97 by Brian Lomasky to add FLOAT datatype =
support.
rem Last Change 08/04/97 by Brian Lomasky to add option to use =
DBA_SEGMENTS for
rem					     sizing the initial and next
rem					     extents, and to include FREELISTS
rem					     and FREELIST GROUPS.
rem
set verify off
set feedback off
set echo off
set pagesize 0
set termout on
set serveroutput on size 100000
rem
rem =
************************************************************************=
***
rem ***** Set the following usesegs flag to Y to use the total number =
of bytes
rem ***** that the segment is currently using (instead of the table's
rem ***** originally-specified INITIAL_EXTENT value) as the value to =
use for
rem ***** the INITIAL_EXTENT in the script file that this script =
creates, so
rem ***** that the table will be recreated in a single extent.  (The =
NEXT_EXTENT
rem ***** will be limited to no more than the INITIAL_EXTENT value.)
rem ***** (Set to 'N' to use the originally-specified INITIAL_EXTENT =
value).
rem =
************************************************************************=
***
rem
def usesegs=3D'N'
rem
rem
select 'Creating table build script...' from dual;
create table tabl_temp (	lineno NUMBER,
				text VARCHAR2(80));
declare
	cursor tab_cursor is select
		upper(owner),
		upper(table_name),
		pct_free,
		pct_used,
		ini_trans,
		max_trans,
		tablespace_name,
		initial_extent,
		next_extent,
		min_extents,
		max_extents,
		freelists,
		freelist_groups,
		pct_increase
		from sys.dba_tables
		where owner !=3D 'SYS'
		order by owner, table_name;
	cursor segments_cursor (s_own VARCHAR2, s_tab VARCHAR2) is select
		bytes
		from sys.dba_segments
		where segment_name =3D s_tab and owner =3D s_own and
		segment_type =3D 'TABLE';
	cursor col_cursor (c_own VARCHAR2, c_tab VARCHAR2) is select
		owner,
		upper(column_name),
		upper(data_type),
		data_length,
		data_precision,
		data_scale,
		nullable,
		default_length,
		data_default,
		column_id
		from sys.dba_tab_columns
		where table_name =3D c_tab and owner =3D c_own
		order by column_id;

	lv_owner		sys.dba_tables.owner%TYPE;
	lv_table_name		sys.dba_tables.table_name%TYPE;
	lv_pct_free		sys.dba_tables.pct_free%TYPE;
	lv_pct_used		sys.dba_tables.pct_used%TYPE;
	lv_ini_trans		sys.dba_tables.ini_trans%TYPE;
	lv_max_trans		sys.dba_tables.max_trans%TYPE;
	lv_tablespace_name	sys.dba_tables.tablespace_name%TYPE;
	lv_initial_extent	sys.dba_tables.initial_extent%TYPE;
	lv_next_extent		sys.dba_tables.next_extent%TYPE;
	lv_min_extents		sys.dba_tables.min_extents%TYPE;
	lv_max_extents		sys.dba_tables.max_extents%TYPE;
	lv_freelists		sys.dba_tables.freelists%TYPE;
	lv_freelist_groups	sys.dba_tables.freelist_groups%TYPE;
	lv_pct_increase		sys.dba_tables.pct_increase%TYPE;
	segment_bytes		sys.dba_segments.bytes%TYPE;
	lv_column_name		sys.dba_tab_columns.column_name%TYPE;
	lv_data_type		sys.dba_tab_columns.data_type%TYPE;
	lv_data_length		sys.dba_tab_columns.data_length%TYPE;
	lv_data_precision	sys.dba_tab_columns.data_precision%TYPE;
	lv_data_scale		sys.dba_tab_columns.data_scale%TYPE;
	lv_nullable		sys.dba_tab_columns.nullable%TYPE;
	lv_default_length	sys.dba_tab_columns.default_length%TYPE;
	lv_data_default		sys.dba_tab_columns.data_default%TYPE;
	lv_column_id		sys.dba_tab_columns.column_id%TYPE;
	lv_lineno		number :=3D 0;
	initial_extent_size	varchar2(16);
	next_extent_size	varchar2(16);
	a_lin			varchar2(80);

	function wri(x_lin in varchar2, x_str in varchar2,
		x_force in number) return varchar2 is
	begin
		if length(x_lin) + length(x_str) > 80
		then
			lv_lineno :=3D lv_lineno + 1;
			insert into tabl_temp values (lv_lineno, x_lin);
			if x_force =3D 0
			then
				return x_str;
			else
				lv_lineno :=3D lv_lineno + 1;
				insert into tabl_temp values (lv_lineno, x_str);
				return '';
			end if;
		else
			if x_force =3D 0
			then
				return x_lin||x_str;
			else
				lv_lineno :=3D lv_lineno + 1;
				insert into tabl_temp values (
					lv_lineno, x_lin||x_str);
				return '';
			end if;
		end if;
	end wri;

begin
	a_lin :=3D '';
	open tab_cursor;
	loop
		fetch tab_cursor into
			lv_owner,
			lv_table_name,
			lv_pct_free,
			lv_pct_used,
			lv_ini_trans,
			lv_max_trans,
			lv_tablespace_name,
			lv_initial_extent,
			lv_next_extent,
			lv_min_extents,
			lv_max_extents,
			lv_freelists,
			lv_freelist_groups,
			lv_pct_increase;
		exit when tab_cursor%notfound;

		if '&&usesegs' =3D 'Y' then
			open segments_cursor (lv_owner, lv_table_name);
			fetch segments_cursor into segment_bytes;
			if segments_cursor%found then
				lv_initial_extent :=3D segment_bytes;
				if lv_next_extent > lv_initial_extent then
					lv_next_extent :=3D lv_initial_extent;
				end if;
			end if;
			close segments_cursor;
		end if;
		a_lin :=3D wri(a_lin, 'create table ', 0);
		a_lin :=3D wri(a_lin, lv_owner || '.' || lv_table_name, 0);
		a_lin :=3D wri(a_lin, ' (', 0);
		if (to_char(lv_ini_trans) =3D '0') then
			lv_ini_trans :=3D 1;
		end if;
		if (to_char(lv_max_trans) =3D '0') then
			lv_max_trans :=3D 1;
		end if;
		open col_cursor(lv_owner, lv_table_name);
		loop
			fetch col_cursor into
				lv_owner,
				lv_column_name,
				lv_data_type,
				lv_data_length,
				lv_data_precision,
				lv_data_scale,
				lv_nullable,
				lv_default_length,
				lv_data_default,
				lv_column_id;
			exit when col_cursor%notfound;
			if lv_column_id <> 1 then
				a_lin :=3D wri(a_lin, ',', 0);
			end if;
			a_lin :=3D wri(a_lin,
				chr(34) || lv_column_name || chr(34), 0);
			a_lin :=3D wri(a_lin, ' ' || lv_data_type, 0);
			if lv_data_type =3D 'CHAR' or lv_data_type =3D 'VARCHAR2' or
				lv_data_type =3D 'RAW'
			then
				a_lin :=3D wri(a_lin,
					'(' || lv_data_length || ')', 0);
			end if;
			if (lv_data_type =3D 'NUMBER' and
				nvl(lv_data_precision, 0) !=3D 0) or
				lv_data_type =3D 'FLOAT'
			then
				if nvl(lv_data_scale, 0) =3D 0 then
					a_lin :=3D wri(a_lin,
						'(' || lv_data_precision || ')',
						0);
				else
					a_lin :=3D wri(a_lin,
						'(' || lv_data_precision ||
						',' || lv_data_scale || ')', 0);
				end if;
			end if;
			if lv_default_length !=3D 0 then
				if lv_default_length < 80 then
					a_lin :=3D wri(a_lin, ' DEFAULT ', 0);
					a_lin :=3D wri(a_lin, lv_data_default, 0);
				else
					dbms_output.put_line(
						'Skipping default clause on ' ||
						'column ' || lv_column_name);
					dbms_output.put_line(
						'  on table ' || lv_table_name);
					dbms_output.put_line(
						'  since length is ' ||
						to_char(lv_default_length));
				end if;
			end if;
			if lv_nullable =3D 'N' then
				a_lin :=3D wri(a_lin, ' NOT NULL', 0);
			end if;
		end loop;
		close col_cursor;
		a_lin :=3D wri(a_lin, ')', 1);
		a_lin :=3D wri(a_lin, '    PCTFREE ' || to_char(lv_pct_free), 0);
		a_lin :=3D wri(a_lin, ' PCTUSED ' || to_char(lv_pct_used), 0);
		a_lin :=3D wri(a_lin, ' INITRANS ' || to_char(lv_ini_trans), 0);
		a_lin :=3D wri(a_lin, ' MAXTRANS ' || to_char(lv_max_trans), 0);
		a_lin :=3D wri(a_lin, ' TABLESPACE ' || lv_tablespace_name, 1);
		a_lin :=3D wri(a_lin, '    STORAGE (', 0);
		/* Calculate extent sizes in Mbytes or Kbytes, if possible */
		if mod(lv_initial_extent, 1048576) =3D 0 then
			initial_extent_size :=3D
				to_char(lv_initial_extent / 1048576) || 'M';
		elsif mod(lv_initial_extent, 1024) =3D 0 then
			initial_extent_size :=3D
				to_char(lv_initial_extent / 1024) || 'K';
		else
			initial_extent_size :=3D to_char(lv_initial_extent);
		end if;
		if mod(lv_next_extent, 1048576) =3D 0 then
			next_extent_size :=3D
				to_char(lv_next_extent / 1048576) || 'M';
		elsif mod(lv_next_extent, 1024) =3D 0 then
			next_extent_size :=3D
				to_char(lv_next_extent / 1024) || 'K';
		else
			next_extent_size :=3D to_char(lv_next_extent);
		end if;
		a_lin :=3D wri(a_lin, ' INITIAL ' || initial_extent_size, 0);
		a_lin :=3D wri(a_lin, ' NEXT ' || next_extent_size, 0);
		a_lin :=3D wri(a_lin, ' MINEXTENTS ' || to_char(lv_min_extents),
			0);
		a_lin :=3D wri(a_lin, ' MAXEXTENTS ' || to_char(lv_max_extents),
			0);
		a_lin :=3D wri(a_lin, ' PCTINCREASE ' || to_char(lv_pct_increase),
			0);
		a_lin :=3D wri(a_lin, ' FREELISTS ' || to_char(lv_freelists), 0);
		a_lin :=3D wri(a_lin, ' FREELIST GROUPS ' ||
			to_char(lv_freelist_groups), 0);
		a_lin :=3D wri(a_lin, ');', 1);
	end loop;
	close tab_cursor;
	commit;
exception
        when others then
                rollback;
                raise_application_error(-20000,
                        'Unexpected error on ' || lv_table_name ||
			', ' || lv_column_name || ': ' ||
                        to_char(SQLCODE) || ' - Aborting...');
end;
/

set termout off