Database rebuild

[Manuel Gómez González]

Hello,

We are planning to rebuild a Oracle 8.0.4 database on a HP-UX 10.20 machine, redimensioning tables and database parameters.

We are new on that operations so we need some help with:

- A script that shows tables and tablespaces actual parameters (disk space, extensions, etc).

- A white paper o similar that allow us to build database fixing new growth parameters, etc.

Can anyone help? Thanks.

[Indira Aramandla]

Hi Manuel,

When you say that you are rebuilding a database with re-dimentioning tables and database parameters, are you intending to increase the table definitions (like initial and next extents....) and what database parameters ere you intending to change.

Because if it is only the table definitions that you intend to change then you can query from DBA_segments and DBA_extents to see which segments had extented to many extents and then verify their initial and next extents, so that you can properly size them.

Then you can export the table data and re-create the table with the new definitions and then import the table data with ignore=y parameter.

So you will not require to recreate the database unless you want to change the database creation parameters like maxdatafiles or control files.....etc


IA

[Indira Aramandla]

There is more...

Attached is a document that explains â How to Determine Real Space used by a Table (Below the High Water Mark)â .

Also refer to Note:298696.1 Fragmentation Issues Due to PCT_INCREASE in metalink.



IA

[Manuel Gómez González]

Only tables and tablespaces definitions.

Thanks

[Yogeeraj_1]

hi,

one quick leap would be to use TOAD (http://www.quest.com/requests/?RequestDefID=49&landing=&adcode=&s= ) to generate all you object script for all your custom schemas.

You can as well generate all info about tablespaces, rbs etc using the same tool

if you need further guidance please let us know.

regards
yogeeraj

[Yogeeraj_1]

hi again,

if you don't want to use the above, you can also try the following:

exp userid=/ owner=your_schema
imp userid=/ indexfile=foobar.sql

easy easy!

NB. Had it been oracle 9i or upper, you could have easily do the following:
select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

regards
yogeeraj

[Yogeeraj_1]

hi again!

did my post answer to your queries?

anyway, here is another tip that you can use to get your tablespace definitions:


$ exp userid=/ full=y

Export: Release 8.1.7.4.0 - Production on Mon Jul 18 13:42:35 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting object type definitions
. exporting system procedural objects and actions^C <<<<<<<<<=== note I did ^C here
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully


$ grep 'CREATE TABLESPACE' expdat.dmp
CREATE TABLESPACE "RBS_TS_01" DATAFILE '/u01/oracle/dev/oradata/rbs_ts_01.dbf'
SIZE 31563776 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_02" DATAFILE '/u01/oracle/dev/oradata/rbs_ts_02.dbf'
SIZE 157392896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_03" DATAFILE '/u01/oracle/dev/oradata/rbs_ts_03.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_04" DATAFILE '/u01/oracle/dev/oradata/rbs_ts_04.dbf'
SIZE 48340992 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "RBS_TS_05" DATAFILE '/u01/oracle/dev/oradata/rbs_ts_05.dbf'
SIZE 26320896 REUSE AUTOEXTEND ON NEXT 1048576 MAXSIZE 2000M EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "USERS" DATAFILE '/u01/oracle/dev/oradata/users.dbf' SIZE
661127168 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT NOLOGGING
CREATE TABLESPACE "DRSYS" DATAFILE '/u01/oracle/dev/oradata/drsys.dbf' SIZE
36700160 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288 ONLINE PERMANENT
CREATE TABLESPACE "TMP" DATAFILE '/tmp/tmp.dbf' SIZE 1048576 REUSE EXTENT
MANAGEMENT LOCAL UNIFORM SIZE 65536
CREATE TABLESPACE "CLAMS" DATAFILE '/u01/oracle/dev/oradata/dev_xlarge.dbf' SIZE
78118912 REUSE AUTOEXTEND ON NEXT 524288 MAXSIZE 2000M EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 524288
...


They'll all be there....


hope this helps too!
regards
yogeeraj

[Manuel Gómez González]

Hi,

Thanks for the info.

Toad seems to be a great tool but need some managing experience.

We prefer to use some scripts.

We are searching for a document in order to interpretate wich of the analyzed tables or tablespaces need some tunning on extents and size cration clauses.

Thanks.

[Indira Aramandla]

Hi Manuel,

Attached is a file that contains Scripts to Report Extents and Contiguous Free Space. If you can anticipate how much more space a particular database object will need, then you can plan where to acquire the additionalspace.These 3 scripts will provide a chance to plan for additional disk space if it is currently not available on the system.

PL/SQL script to determine how much contiguous free space is available for each tablespace in the database. Oracle will acquire space by searching first for exact fit, then next best fit, and lastly coalesce if possible

Currently, the tools to remedy fragmentation are IMP/EXP (import and export) utilities. You may also drop segments that lie between free extents, create a dummy object that will require a segment of just the right size to cause the separate segments to be coalesced, and then drop the dummy object.

From the information retrieved in the above three scripts, you can track the objects in each tablespace and make sure there is enough space for it to grab at least another extent. Also, it is important to make sure the object is not getting close to the maximum number extents as the database increases in size.


IA

[Indira Aramandla · Accepted solution]

Hi,

If you need to see which tables have extended to many extents, as they might be not defined with proper initial and next extents sizes for this as I mentioned before, you can query from DBA_SEGMENST and DBA_EXTENTS. And sometimes the table data is deleted on a regular basis (eg: from purge jobsâ ¦) such tables need to be reorged by export and import, so as to reduce the extents and coalesce the sapce. Then alter the tablespace to coalesce the free space.

Attached document is a not from metalink (Note:10640.1 ) â Extent and Block Space Calculation and Usage:. This document explains how to accurately size the data dictionary, user tables,
user indexes, rollback segments, redo logs.

I hope this helps too.

IA