Skip to Content
avatar image
Former Member

tablespace redesign

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?



Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 26, 2009 at 03:09 AM

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


    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 27, 2009 at 12:13 PM


    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.



    Add comment
    10|10000 characters needed characters exceeded