tablespace redesign

[Former Member]

We have completed our BW upgrade to BI 7.0 and we are planning for Unicode Export/Import shortly.

We have big chunk of tablespace space as free space(nearly 50%), which we would like to plan this way.

All i need is the expert opinion on how SAP treats the new tablespaces.

Our DBA is saying to move all the tables from the standard SAP tablespaces to another tablespace name instead of two prone approach(moving the tables from standard tablespace to another intermediate tablespaces, drop the original sap tablespace and recreate again, then move the tables from the intermediate tablespace).This way he can shrink the free space.

Will it have any impact on our unicode import?

Regards,

VJ

[markus_doehr2 · Accepted solution]

During the Unicode import the database will be recreated by sapinst - you can there define which tablespace layout you want to use.

Markus

[Former Member]

Hi,

There are several options to do this.

1. as your DBA suggested.

2. Coalesce the tablespace and then resize the datafile.

sqlplus /nolog

SQL> conn sys as sysdba

SQL> alter tablespace <tablespace_name> cloalesce;

Then check the current datafile size

SQL> select tablespace_name, file_id, file_name, bytes, maxbytes,autoextensible from dba_data_files;

Check the used space of the datafile

SQL> select file_id, sum(BYTES) from dba_extents group by file_id;

Resize the datafile

SQL> ALTER DATABASE DATAFILE <file_id>|'<file_name>' RESIZE <value>;

3. Export the database, drop and recreate the tablespace, import the data.

to schema level export

exp <user id>/<password>@<SID> file=<dump_file_name> log=<log_file_name> owner=<schema_name>

using BR*tool/SQL command you can drop and recreate tablespace.

Import the dump file to the database

imp <user id>/<password>@<SID> file=<dump_file_name> log=<log_file_name> fromuser=<schema_name> touser=<schema_name> ignore=y commit=y

You can do this using datapump tool also.

Thanks

Manoj